Using VBA to create Vlookup Commands..

I am essentially trying to use VBA to perform a Vlookup command, that increments the row, for example B2, B3, B4 and such.

I have created the code below, to increment the row in the Vlookup, however it keeps debugging. Has anyone run into this issue? Thanks.

lastsymbol = Cells(Rows.Count, "H").End(xlUp).Row
  For I = 2 To lastsymbol

      With Sheets("Data")
.Cells(I, 8) = "=VLOOKUP(B" & I & ",'C:\Master Lookupxls]Project -> Year Lookup'!$A$1:$B$65536,2,0)"""
      End With
      
   Next I

That worked, but a popup that says "Update Values: Master Lookup Sheet" comes up that asks for the location again of the excel file. It does that for each iteration of the for loop, i.e. for each row.

Is there anyway the vba can run by by-passing this popup or entering in the location in the popup. Ty

That worked. Thanks

This code lets you setup the Vlookup for the first row and then does an autofill for the rest of the rows below it. Thanks

lastsymbol = Cells(Rows.Count, "H").End(xlUp).Row


      With Sheets("Data")
      
      
.Cells(2, 8).Formula = "=VLOOKUP(B2" & ",'C:\[Master Lookup Sheet.xls]Project -> Year Lookup'!$A$1:$B$65536,2,0)"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H2447")
    Range("H2:H2447").Select
    
      End With

It seems to work, however the pop-up still comes up at the end that says "Update Values: Master Lookup Sheet", looking for the location of the lookup file. Is there anyway to automatically hit cancel or type in the file path name in this popup?

I haven't looked any further but there are a couple of things to start with I think.

.Cells(i, 8).Formula= "=VLOOKUP(B" & I & ",'[C:\Master Lookupxls]

You could try this to get rid of that pop-up:

Application.AskToUpdateLinks = False