String inside an excel vba formula

0
votes

I have a VBA code that insert the formula below in a cell. I want to combine A7 and string "EBF" into one string inside the VLOOKUP formula but it seems to keep giving me error.

This is what I want in the excel cell:

=D7-IFERROR (VLOOKUP (A7&"CAF",sheet1!A1:B31,2,FALSE),0)

This is my VBA Code to insert the formula into the cell:

Range("H7") = "=D7-IFERROR(VLOOKUP(A7 & ""EBF"" & ",sheet1!A2:B31,2,FALSE),0)"
2
vbaexcel
Why do you have an extra & " after ""EBF""? – Chronocidal

2 Answers

4
votes

Like (Remembering to use the .Formula to assign)

Range("H7").Formula = "=D7-IFERROR(VLOOKUP(A7 & ""CAF"" ,sheet1!A2:B31,2,FALSE),0)"
0
votes

"=D7-IFERROR(VLOOKUP(A7&""EBF"",sheet1!A2:B31,2,FALSE),0)"