Change path name inside a VLOOKUP dynamically based on cell value VBA

Hi,

I'm hoping someone can help me provide the missing piece to the code below.
I've tried to use a do-while loop but I can't make it work. I posted the part of the code that is working below.

Basically, I have to insert a formula in all the cells in column AN.
The macro i have changes a variable in the file path that's inside a VLOOKUP formula.
The variable is the data in Column AB. (it's assigned in the code as L1)

The formula in AN25 should use the value in AV25 as the value inside the file path.

Sub InsertFormulas()
Dim x As String
Dim L1 As String
Dim ShtName As String




L1 = ActiveCell.Offset(0, 8).Value
ShtName = ActiveSheet.Name


x = "C:\Users\carvajalk\Desktop\[" & L1 & ".xlsx]" & ShtName


ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-35],'" & x & "'!R23C5:R29C40,36,0)"


End Sub

thank you to anyone who can help.

Hi Carim,

thank you for your initial answer. However, i was actually hoping to employ a loop where the value of L1 will change depending on where row it is in.

e.g. Active cell is in an24, so the value of L1 is based on the cell value of AV24. Then the loop runs again and the active cell becomes AN25 and the L1 should now be based on AV25

i'm having a hard time coding the loop because the L1 becomes fixed at AV24, instead of moving on to the next row.

Absolute life saver! The macro worked. Thank you so much for your help Carim!