im not able to enter this formula using VBA

[Mohammednt0]

good day

# point 1 - whats the correct way to enter this formula using vba

the quotation marks (") is the problem, so when i enter this part of the formula ("No"),"Yes") the vba code will not work

Code: Select all

Sub insert_New_Material_formula()

   Range("I2:I2000").Formula = "=IFERROR(IF(VLOOKUP(C2,'[5. Inventory Analysis Report.xlsm]Bayad'!$B:$D,3,FALSE),"No"),"Yes")"
    
End Sub

# point 2 - column "I" range should be as long as column A, im not sure how to do this


thanks in advance

[HansV]

String values inside a quoted string must be enclosed in double double quotes: ""No"" and ""Yes"".
This should run without error, but I'm not sure it will do what you want. The formula will return FALSE if the VLOOKUP part returns "" or 0.

Code: Select all

Sub insert_New_Material_formula()
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("I2:I" & LastRow).Formula = "=IFERROR(IF(VLOOKUP(C2,'[5. Inventory Analysis Report.xlsm]Bayad'!$B:$D,3,FALSE),""No""),""Yes"")"
End Sub

[Mohammednt0]

thanks it worked