Excel macro - same formula, different row

Panayotov · Nov 29th 2020

Hello everyone!

I recently discovered a macro recorder in excel and use it to save time with some annoying repetitive actions.

Unfortunately, with one of them I cannot succeed. I am trying to enter data with a formula. The data is visually in the same place, but in different rows.

The idea is as follows:

In the specified table I hide the first row, go to the bottom in the first cell (C93) I write the word "board", the second cell (C94) I equate to cell A2, in the third (C95) I write "1", then the fourth, fifth, sixth and seventh fields I equate to cells F11, P11, N11 and L11.

What I'm trying to do is hide the rows from 1 to 14, mark cell D93 and repeat the same operations for the next data, but the macro remembers my cell number, not its position.

Can anyone help me with ideas?

Thanks in advance!

Here there is the macro that I recorded:

Code

Sub MDB_Data()
'
' MDB_Data Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
'
ActiveCell.FormulaR1C1 = "board"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-92]C[-2]"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-85]C[3]"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-86]C[13]"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-87]C[11]"
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-88]C[9]"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Display More

**royUK** · Nov 29th 2020

I'm not really sure what you are doing. Your code works from the ActiveCell. I have amended the code to start in the active cell then each subsequent change is made to the row below.

Code

Sub MDB_Data()
'
' MDB_Data Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
With ActiveCell
.FormulaR1C1 = "board"
.Offset(1, 0).FormulaR1C1 = "=R[-92]C[-2]"
.Offset(2, 0).FormulaR1C1 = "1"
.Offset(3, 0).Range("A1").FormulaR1C1 = "=R[-85]C[3]"
.Offset(4, 0).FormulaR1C1 = "=R[-86]C[13]"
.Offset(5, 0).FormulaR1C1 = "=R[-87]C[11]"
.Offset(6, 0).FormulaR1C1 = "=R[-88]C[9]"
End With
End Sub

Display More

Panayotov · Nov 29th 2020

Hi and thanks for your response!

I'm trying to get link from from cell A2, F11, P11, N11 and L11 and to specific cell in order to receive this output on specific place.

board

A2

F11

P11

N11

L11

I need data in this order so I process it further. In attachment I marked in yellow the output I need. Sometimes I have to make this for 4-5 boards data, sometimes for 70-80 boards. So I am trying to save some times.

**royUK** · Nov 29th 2020

It's not clear what you want. Do you mean that the cells the formulas are referring to are fixe, i.e. always the same cells?

Panayotov · Nov 29th 2020

Something like that. The cells are always to the same location but different row .

For example first column of I refer to some data from row 2 (A2) and row 11 (F11, P11, N11, L11),

second column to row 15 (A15) and row 24 (F24, P24, N24, L24),

third column row 28 (A28) and row 37 (F37, P37, N37, L37),

.... and so on.

For me it would be better if I copy the range from A2 to P11, go to desired cell and run the macro so that the data arrange in column. The data from cells should be as link, not value.

column 1 column 2 column 3

board board board

1 1 1

A2 A15 A28

F11 F24 F37

P11 P24 P37

N11 N24 N37

L11 L24 L37

After that go to second range A15 - P24, mark second cell and run the macro.

The distance between different range is always the same, but destination cell maybe different.

Excel macro - same formula, different row

Share

Tags