Make row in VBA formula variable

[schroederdj]

I want to use the below XLOOKUP formula in VBA but as I loop through additional rows I want the R[-1]C[2] (in red below) to stay fixed. (like when you use $K$1 in a regular excel formula. Is that possible? Another solution I thought of was to use a counter to take the R[-1]C[2] and subtract another R1 each loop which would essentially keep it at the original fixed position. Thanks for your help

Sub ListSheet()

Dim ws As String

'get previous day tab name
ws = ActiveSheet.Next.Name
Range("k1:k1").Select
ActiveCell = ws


'XLOOKUP Formula to previous tab report

Range("I2").Select

Do While ActiveCell.Offset(0, -1) <> ""

ActiveCell.Formula2R1C1 = _
"=XLOOKUP(RC[-5],(INDIRECT(""'""&R[-1]C[2]&""'!D:D"")),(INDIRECT(""'""&R[-1]C[2]&""'!I:I"")))"
ActiveCell.Offset(1, 0).Select

Loop


End Sub

 

[Joe4]

No need for loops or Selects, which slow your code down.
See if this does what you want:

Sub ListSheet()

    Dim ws As String
    Dim lr As Long

'   Get previous day tab name
    ws = ActiveSheet.Next.Name
    Range("K1").Value = ws
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Apply lookup formula to column I down to last row
    Range("I2:I" & lr).FormulaR1C1 = "=XLOOKUP(RC[-5],(INDIRECT(""'""&R[-1]C[2]&""'!D:D"")),(INDIRECT(""'""&R[-1]C[2]&""'!I:I"")))"
    
End Sub

 

[Tetra201]

... I want the R[-1]C[2] (in red below) to stay fixed. (like when you use $K$1 in a regular excel formula. Is that possible?

Try using R1C11.

 

[schroederdj]

No need for loops or Selects, which slow your code down.
See if this does what you want:

Sub ListSheet()

    Dim ws As String
    Dim lr As Long

'   Get previous day tab name
    ws = ActiveSheet.Next.Name
    Range("K1").Value = ws
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Apply lookup formula to column I down to last row
    Range("I2:I" & lr).FormulaR1C1 = "=XLOOKUP(RC[-5],(INDIRECT(""'""&R[-1]C[2]&""'!D:D"")),(INDIRECT(""'""&R[-1]C[2]&""'!I:I"")))"
   
End Sub

This was helpful but but in each copied cell down of the xlookup formula, K1 did not stay constant. I need each row to be $K$1, but the formula is making it K1, k2, k3, k...

 

[Joe4]

OK, try this update:

Sub ListSheet()

    Dim ws As String
    Dim lr As Long

'   Get previous day tab name
    ws = ActiveSheet.Next.Name
    Range("K1").Value = ws
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Apply lookup formula to column I down to last row
    Range("I2:I" & lr).FormulaR1C1 = "=XLOOKUP(RC[-5],(INDIRECT(""'""&R1C[2]&""'!D:D"")),(INDIRECT(""'""&R1C[2]&""'!I:I"")))"
    
End Sub

 

[schroederdj]

OK, try this update:

Sub ListSheet()

    Dim ws As String
    Dim lr As Long

'   Get previous day tab name
    ws = ActiveSheet.Next.Name
    Range("K1").Value = ws
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Apply lookup formula to column I down to last row
    Range("I2:I" & lr).FormulaR1C1 = "=XLOOKUP(RC[-5],(INDIRECT(""'""&R1C[2]&""'!D:D"")),(INDIRECT(""'""&R1C[2]&""'!I:I"")))"
   
End Sub

Thank you. This is exactly what I was looking for. And I appreciate the code to do the copy vs the loop. The loop was very slow

 

[Joe4]

You are welcome.

Note: When marking a post as the solution, you want to mark the original post containing the solution, not your own post acknowledging that some other post was the solution.
I have updated this for you on this thread.