VBA Macro not accurately inserting VLOOKUP into Excel cells

Asked Oct 12 '21 at 12:00

Active Oct 12 '21 at 12:00

Viewed 17 times

In the macro I have these lines (and lots more like them, 66 columns in all):

Sheets("Amend").Select
           Range("B3").Value = "=INDEX(Data!A3:A100,G2)"
           Range("B4").Value = "=IF(VLOOKUP($B$3,Data,2,FALSE)="","",VLOOKUP($B$3,Data,2,FALSE))"

Range("B3").Value = "=INDEX(Data!A3:A100,G2)"

is inserted correctly but

Range("B4").Value = "=IF(VLOOKUP($B$3,Data,2,FALSE)="","",VLOOKUP($B$3,Data,2,FALSE))"

is inserted as:

Range("B4").Value = "=IF(VLOOKUP($B$3,Data,2,FALSE)=",",VLOOKUP($B$3,Data,2,FALSE))"

which no longer functions as a lookup and shows as FALSE.

Any ideas why "","" is being inserted as ","? Is there another way to say if nothing put nothing else lookup?

In case the Excel/VBA version is the problem, I am using Excel 2010 but the person I'm doing the project for is using 365, so would the macro run OK on 365? Thank you

edited Oct 12 '21 at 12:00

braX

11,506
5
20
33

asked Oct 12 '21 at 12:00

Keith Cooper

2

`="""","""",` in the formula is `=""."".` in Excel. – VBasic2008 Oct 12 '21 at 12:06
You need to double the quotes within the formula: `Range("B4").Value = "=IF(VLOOKUP($B$3,Data,2,FALSE)="""","""",VLOOKUP($B$3,Data,2,FALSE))" ` – Rory Oct 12 '21 at 12:10
2

Note that there is `Range.Formula` which should be used for formulas instead of `Range.Value`. – Pᴇʜ Oct 12 '21 at 12:11
Amended macro in line with both suggestions, now works like a dream. Thanks – Keith Cooper Oct 12 '21 at 14:01

VBA Macro not accurately inserting VLOOKUP into Excel cells

0 Answers0