CODEWITHSUNDEEP
vbaexcel
excelguy
excelguy

Reputation: 1624

VBA, IF and Vlookup statement

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")

Upvotes: 1

Views: 88

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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 = ....

Upvotes: 1

Related Questions