# Macros with Formulas that reference different cells

#### nicklbailey

##### New Member
I am trying to write a macro with a formula whose reference varies with the cell and have not been able to find any clear explanation of how this is done (my guess is it's really obvious and I am just not seeing it.
This is what I have now
Code:
``````Sub Test()
'
' Test Macro
'

'
Dim Row As Integer
For Row = 1 To 500
Dim RefCell As Range
Set RefCell = Cells(Row, 11)
RefCell.FormulaR1C1 = "=MATCH(RC[-4],H\$1:H\$291,0)"
Cells(Row, 12).FormulaR1C1 = "=INDEX(A1:M1000,VALUE(RC[-1]),9)"
Next Row

End Sub``````

I want the first formula to be "=MATCH(G1,H\$1:H\$291,0)" for K1, "=MATCH(G2,H\$1:H\$291,0)" for K2 and so forth.
Similarly the second formula should vary with the row.
Is there an easy way to do this?

Best

Nick

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Figured it out, go ahead a delete the post

Figured it out, go ahead a delete the post
Is this what you came up with?
Code:
``````Sub Test()
'
' Test Macro
'
Dim Row As Integer
For Row = 1 To 500
Dim RefCell As Range
Set RefCell = Cells(Row, 11)
RefCell.FormulaR1C1 = "=MATCH(RC[-4],R1C[-3]:R291C[-3],0)"
Cells(Row, 12).FormulaR1C1 = "=INDEX(RC[-11]:R[999]C[1],VALUE(RC[-1]),9)"
Next Row
End Sub``````
As you must have discovered, you cannot mixe A1 notation with R1C1 notation in a formula being assigned to the FormualR1C1 property. A quick way to get the correct R1C1 formula is to write the A1 notation version of the formula in the cell it should go in, then go into your Excel version's Options dialog and find the option titled R1C1 reference style (it's on the General tab for XL2003, the Formulas item in XL2007 and XL2010... I don't have XL2013 but it is probably the same as XL2010) and put a check mark in it, then return to the worksheet and copy the R1C1 notation from the cell you put the formula in (don't forget to go back into Options and remove the check mark in order to return your workbook to A1 notation).

Replies
4
Views
137
Replies
16
Views
516
Replies
3
Views
276
Replies
0
Views
136
Replies
0
Views
331

1,203,757
Messages
6,057,178
Members
444,911
Latest member
Uncommon1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back