VBA insert formula into cell with special character containing ""

[MBM2016]

Okay, so I am expanding my vlookup criteria and I need to insert a "-" within a formula in A1 - (for code details please see this)

This is what I think my vba should look like:

wb1.ActiveSheet.[A1].Value = ("=VLOOKUP(" & cID & rID & "&" & [COLOR=#ff8c00]""-""[/COLOR] & LastCol & "," & map & "," & i & ",FALSE)")

yet my editor keeps changing it to this and it does not work:

wb1.ActiveSheet.[a1].Value = ("=VLOOKUP(" & cID & rID & "&" & [COLOR=#ff0000]"" - ""[/COLOR] & LastCol & "," & map & "," & i & ",FALSE)")

This is what I am trying to accomplish:
=VLOOKUP(A15&"-"&B15,'C:\Users\x165422\Desktop\New folder\[Reference.xlsx]Ref'!$A$1:$I$15,4,FALSE)

Does anyone know what I am doing wrong or how I should I approach this problem?


Thank you!

 

[Aaron Blood]

I think there is an ampersand missing in your VBA code.

"=VLOOKUP(" & cID & rID & "&" & ""-"" & LastCol & "," & map & "," & i & ",FALSE)"
"=VLOOKUP(" & cID & rID & "&""-""&" & LastCol & "," & map & "," & i & ",FALSE)"

The first string above is copied from your email (what you think it should look like) and the second line is what the string should be.

 

[MBM2016]

Great, thank you Aaron Blood!! it worked, I didn't see how taking out

[COLOR=#333333]"&" [/COLOR][COLOR=#ff0000]&[/COLOR][COLOR=#333333] ""-"" [/COLOR][COLOR=#ff0000]&[/COLOR]

and adding the additional ampersand would do it.

I will keep this in mind , thanks again!