VBA using vlookup of indirect range for formula in cell

Question

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!

Answer 1

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