VBA Excel vlookup in loop problem

Question

I have a problem with my VBA code in an excel spreadsheet containing orders. Each row contains a customer number, which I use to look up the customer email address, contained in a different sheet in the workbook.

The vlookup code works fine for a single cell, but the problem is when I try to loop through all of the rows of the spreadsheet. The Excel formula for a single cell is, e.g.,

=VLOOKUP(B2,Customers!A2:D1000,4,FALSE)

The VBA code generated for this is:

    Range("M2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-11],Customers!RC[-12]:R[999]C[-9],4,FALSE)"

Incorporating this into a loop, after selecting the starting cell, I have the following:

Cells(2, 13).Select
Do
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],Customers!RC[-12]:R[999]C[-9],4,FALSE)"
  ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -10))

The problem is that I want the "table array" to be fixed, not relative to the cell whose value is being looked up. But I absolutely can't figure out how to do it. If I change the code as follows, I get a run-time error:

ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-11],Customers!A2:D1000,4,FALSE)"

I have tried quoting, unquoting, setting a range variable, using the range variable with .address... can someone please help?

Thank you so much.

transistor1 transistor1 · Accepted Answer · 2011-08-18T00:18:05

I'm pretty sure the brackets in your R1C1 formula indicate that you are specifying a relative range (especially with the negatives in them). If you want to specify an absolute range, you need to specify the R1C1 cells without brackets; e.g. R2C2:R4C4.

As a simple example:

Sub test()
  Sheet1.Range("C5").FormulaR1C1 = "=VLOOKUP(1,R1C1:R3C3,2,FALSE)"
End Sub

Running this gives you an absolute "A1"-style formula in cell C5.

I think your problem might be:

Customers!RC[-12]:R[999]C[-9]

Because it is a relative range. You have to explicitly specify where your data table is; e.g. Customers!RC12:R999C9 (you need to figure out where it is on your sheet).

An easy way of figuring this out is highlighting your data table on your worksheet, then switch to the Visual Basic Editor and manually run this (put your cursor inside of the Sub, and press the 'Play' button or go to Run->Run).

Sub test2()
    Dim r As Range
    Set r = Application.Selection
    InputBox "your r1c1 range:", , r.Address(True, True, xlR1C1)
End Sub

VBA Excel vlookup in loop problem

1 Answers