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.