Macros with Formulas that reference different cells

[nicklbailey]

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

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

 

[nicklbailey]

Figured it out, go ahead a delete the post

 

[Rick Rothstein]

Figured it out, go ahead a delete the post

Is this what you came up with?

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).