CODEWITHSUNDEEP
vbaexcelvlookup
Tawm
Tawm

Reputation: 545

VBA: Using Dims in a Vlookup

I'm trying to do a vlookup in VBA and I'm getting an Application-defined or object-defined error run-time error.

I rewrote the line with integers and strings instead of Dims and it works fine, but I need to make it variable.

'Throws Error
rc = -6
tempwb = "Supplier Master - Location - 08-13-15.xls"
acol = 1
zcol = 14
wRange.FormulaR1C1 = "=VLOOKUP(RC[rc],'[" & tempwb & "]Sheet1'!C" & acol & ":C" & zcol & "," & ((zcol - acol) + 1) & ",FALSE)"


'Works
wRange.FormulaR1C1 = "=Vlookup(RC[-6],'[Supplier Master - Location - 08-13-15.xls]Sheet1'!C1:C14,14,FALSE)"

Upvotes: 1

Views: 99

Answers (3)

Don Jewett
Don Jewett

Reputation: 1967

It was easy enough to diagnose by doing this:

Dim sFormula As String

sFormula = "=VLOOKUP(RC[rc],'[" & tempwb & "]Sheet1'!C" & acol & ":C" & zcol & "," & ((zcol - acol) + 1) & ",FALSE)"

Debug.Print sFormula

wRange.FormulaR1C1 = sFormula

Upvotes: 0

DaveMac
DaveMac

Reputation: 76

Try changing your string so you include the rc variable.

wRange.FormulaR1C1 = "=VLOOKUP(RC[" &rc&"],'["

Upvotes: -1

HarveyFrench
HarveyFrench

Reputation: 4568

try changing

wRange.FormulaR1C1 = "=VLOOKUP(RC[rc],

to be 

wRange.FormulaR1C1 = "=VLOOKUP(RC[" & rc & "],

Done?

Upvotes: 4

Related Questions