CODEWITHSUNDEEP
vbaexcelvlookupnamed-ranges
KMO
KMO

Reputation: 13

Refering to a predefined range through r1c1 formulation

I have to look up some values from another range in another worksheet. I have defined the range as "x", and i would like to use it in the r1c1 formulation, see example:

Set x = extwbk.Worksheets("ark1").Range("A1:d400")
With twb.Sheets(filename)
        .Range("k2:k" & n).FormulaR1C1 = "=VLOOKUP(RC[-10],x,4,False)"
        .Range("l2:l" & n).FormulaR1C1 = "=VLOOKUP(RC[-11],x,4,False)"

I have tried to do the application.vlookup, which works, but too slow, as i have to loop through 4000+ rows twice I will need to use the range x, as the range should be flexible depending on the lookup table, exmaple: 

Set x = extwbk.Worksheets("ark1").Range("A1:d" & rows)

Any suggestions how i can refer to the range x in my lookup function? Thanks!

Upvotes: 1

Views: 464

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

You are setting x range as Set x = extwbk.Worksheets("ark1").Range("A1:d400") , and you want to use it in a formula in R1C1 type, you can get the address from your x range by x.Address(False, False, xlR1C1).

Try using the 2 lines below:

.Range("K2:K" & n).FormulaR1C1 = "=VLOOKUP(RC[-10]," & x.Address(False, False, xlR1C1) & ",4,False)"
.Range("L2:L" & n).FormulaR1C1 = "=VLOOKUP(RC[-11]," & x.Address(False, False, xlR1C1) & ",4,False)"

Following @Rory comment and PO, if you are referring to an external workbook then you need to add a 4th parameter, and also to look for absolute range:

.Range("K2:K" & n).FormulaR1C1 = "=VLOOKUP(RC[-10]," & x.Address(True, True, xlR1C1, True) & ",4,False)"
.Range("L2:L" & n).FormulaR1C1 = "=VLOOKUP(RC[-11]," & x.Address(True, True, xlR1C1, True) & ",4,False)"

Upvotes: 1

Related Questions