schroederdj
New Member
- Joined
- Jun 21, 2022
- Messages
- 14
- Office Version
- 365
- Platform
- Windows
I have the following formula in VBA code that gets copied down to all rows. Everything works in the code but I need J4 to change to J5, J6, J...... as the formula is copied down.
Range("K4:K" & lr).FormulaR1C1 = "=IFERROR(IF(XLOOKUP(RC[-7],(INDIRECT(""'""&R1C[2]&""'!D:D"")),(INDIRECT(""'""&R1C[2]&""'!I:I"")))=-J4,""MATCH"",""PARTIAL MATCH""),0)"
Can anyone please help with how to make J4 variable as copied down?
Here is the entire code:
Sub Match_OS()
Sheets("Retail Prior OS").Select
Range("m1").Select
ws1 = Sheets(1).Name
ActiveCell = ws1
Selection.NumberFormat = "@"
'XLOOKUP Formula to previous report
Range("k4").Select
' Find last row with data in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row
' Apply lookup formula to column J down to last row
Range("K4:K" & lr).FormulaR1C1 = "=IFERROR(IF(XLOOKUP(RC[-7],(INDIRECT(""'""&R1C[2]&""'!D:D"")),(INDIRECT(""'""&R1C[2]&""'!I:I"")))=-J4,""MATCH"",""PARTIAL MATCH""),0)"
End Sub
Range("K4:K" & lr).FormulaR1C1 = "=IFERROR(IF(XLOOKUP(RC[-7],(INDIRECT(""'""&R1C[2]&""'!D:D"")),(INDIRECT(""'""&R1C[2]&""'!I:I"")))=-J4,""MATCH"",""PARTIAL MATCH""),0)"
Can anyone please help with how to make J4 variable as copied down?
Here is the entire code:
Sub Match_OS()
Sheets("Retail Prior OS").Select
Range("m1").Select
ws1 = Sheets(1).Name
ActiveCell = ws1
Selection.NumberFormat = "@"
'XLOOKUP Formula to previous report
Range("k4").Select
' Find last row with data in column A
lr = Cells(Rows.Count, "A").End(xlUp).Row
' Apply lookup formula to column J down to last row
Range("K4:K" & lr).FormulaR1C1 = "=IFERROR(IF(XLOOKUP(RC[-7],(INDIRECT(""'""&R1C[2]&""'!D:D"")),(INDIRECT(""'""&R1C[2]&""'!I:I"")))=-J4,""MATCH"",""PARTIAL MATCH""),0)"
End Sub