VBA, IF and Vlookup statement

Question

I have the following formula,

=IF(valumeasure3!E2="Buy Notional Amount",VLOOKUP(C2,valumeasure3!C:U, COLUMNS(C:U),FALSE),0)

trying to automate this formula in vba, I get a syntax error. Vlookup formulas work fine, but as soon as I do the IF statement i get syntax error. I believe it is to do with the =

Range("R2").Select
        ActiveCell = "=IF(valumeasure3!E2="Buy Notional Amount",VLOOKUP(C2,valumeasure3!C:U, COLUMNS(C:U),FALSE),0)"   ''buy currency amt
        Selection.AutoFill Destination:=Range("R2:R4182")

`"Buy Notional Amount"` needs to be `""Buy Notional Amount""` — Scott Craner, Sep 27 '17 at 19:56

K.Dᴀᴠɪs · Accepted Answer · 2018-01-31T14:26:49.363

1

In VBA, when you use quotation marks you are setting a boundary to a string. You should use double quotes.

Range("R2").Select
    ActiveCell = "=IF(valumeasure3!E2=""Buy Notional Amount"",VLOOKUP(C2,valumeasure3!C:U, COLUMNS(C:U),FALSE),0)"    ''buy currency amt
    Selection.AutoFill Destination:=Range("R2:R4182")

However, a suggestion is that I would recommend that you not use .Select and ActiveCell whenever possible.

Dim Rng as Range
Set Rng = Range("R2")
Rng.Value = ....

edited Jan 31 '18 at 14:26

answered Sep 27 '17 at 19:59

K.Dᴀᴠɪs

9,945
11
33
43

instead of Activecell, seletion, and select I can just use rng right? – excelguy Sep 27 '17 at 20:09
@excelguy Yes that should work for you.You wouldn't need the `Range("R2").Select` line at all. – K.Dᴀᴠɪs Sep 27 '17 at 20:13
@excelguy take a look at this question about avoiding using Select and ActiveCell https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Maciej Lipinski Jan 31 '18 at 14:33

VBA, IF and Vlookup statement

1 Answers1