Using IFERROR with VLOOKUP

2
votes

I am adding a formula into a spreadsheet via VBA that uses iferror and vlookup. Below is the code from the VBA

 LastRow = Cells(Rows.Count, 1).End(xlUp).Row
 Range("O2:o" & LastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,'Controls'!a:b,2,FALSE),""Missing"")"

But when I look back into the spreadsheet, I find the following formula in the cells

  =IFERROR(VLOOKUP($A2,Controls!A:(B),2,FALSE),"Missing")

which always results in a value of "Missing". When I edited the formula in the sheet and removed the parens from the B in the lookup range, the formula resolved correctly by finding the value in the Controls sheet.

I've tried changing the lookup range from A:B to A1:B500, $A$1:$B$500, and $A:$B. I also tried breaking it up and using various concatenations.

My question is ... How can I get VBA to NOT add the parens around the B in my lookup range.

2
excelvbavlookup

2 Answers

2
votes

Your problem comes from the fact that you put a regular address (A:B) into a R1C1-Formula. Even if your lookup-range is in another sheet, Excel expect the address in R1C1-notation. Your formula would look like

dim formula as string
formula = "=IFERROR(VLOOKUP(RC1,Controls!C[-14]:C[-13],2,FALSE),""Missing"")"
Range("O2:o" & LastRow).FormulaR1C1 = formula

as this is super ugly, I would suggest you define a name for the range Controls!A:B. Then you can change the formula simply to

formula = "=IFERROR(VLOOKUP(RC1,MyControls,FALSE),""Missing"")"
0
votes

I think you would need to use .Formula(...) instead of .FormulaR1C1(...) and use uppercase on your string like ...A:B...

Else, you could do the following:

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("O2:o" & LastRow).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,'Controls'!a:b,2,FALSE),""Missing"")"
Dim cell As Range
For Each cell in Range("O2:O" & LastRow)
    cell.Formula = Replace(cell.Formula, "(B)","B")
Next cell