How to use relative reference in a formula using vba

[hutch27]

Hello -

I'm using an If-Then function in vba to insert a formula into specific cells based on the function. However, I'm having trouble with vba using an absolute rather than relative reference.

Here is my code:

Sub Macro12()
Dim i As Long
For i = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If ActiveSheet.Cells(i, 3) = "Lookup Value" Then
ActiveSheet.Range("H" & i).Formula = "=Table_Cash[@[Amount]]-G32"
End If
Next i
End Sub

If you notice in the formula, "=Table_Cash[@[Amount]]-G32" is a value from a table (this table is variable) and as such, uses relative reference - which i'm fine with. Alternatively, "G32" is an absolute reference not in a table, and, as such, uses absolute reference. How can i make it such that G32 is a relative reference?

Also - How would i add a subtotal to the code?

 

[Marcelo Branco]

Assuming table's headers in row 1, maybe this...

Sub Macro12()
Dim i As Long
For i = [COLOR=#ff0000]2[/COLOR] To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    If UCase(ActiveSheet.Cells(i, 3)) = "LOOKUP VALUE" Then
        ActiveSheet.Range("H" & i).Formula = "=Table_Cash[@[Amount]]-G" & [COLOR=#ff0000]30 + i[/COLOR]
    End If
Next i
End Sub

Hope this helps

M.

 

[hutch27]

People like you deserve Nobel Prizes!

 

[Marcelo Branco]

People like you deserve Nobel Prizes!

Thank you!

M.