I'm sure it's an error with the way I'm using my quotes and apostrophes but for the life of me I can't fix it. I'm running a loop in VBA to add a vlookup in an indirect range. When I just use an indirect cell based on the row number I'm in, I have no errors (see below):
For n = 6 To 77
Range("A" & n).Formula = "=IF(INDIRECT(""'"" & B1 & ""'!A" & n & """)="""","""",INDIRECT(""'"" & B1 & ""'!A" & n & """))"
Next n
Where B1 is the cell with the name of the sheet I want to reference.
When I try to incorporate the same logic into a VLOOKUP, I get a run-time error:
For n = 6 To 77
Range("C" & n).Formula = "=VLOOKUP(A" & n & ",INDIRECT(""'"" & B1 & ""'!A1:H76""" & "),3,0))"
Next n
I'm sure it's something with the quotes but I just can't figure it out, please help!
this is what you wanted, where sheet name is in b1 and table array A1:H76 is in cell b2
Dim n As Long
For n = 6 To 77
Range("c" & n).Formula = "=VLOOKUP(a" & n & ", INDIRECT(""'"" & b1 & ""'!"" & b2),3,0)"
Next n