CODEWITHSUNDEEP
vbavlookupexcel-indirect
Meghan
Meghan

Reputation: 125

VBA using vlookup of indirect range for formula in cell

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!

Upvotes: 1

Views: 1167

Answers (1)

luckyguy73
luckyguy73

Reputation: 1939

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

Upvotes: 1

Related Questions