im not able to enter this formula using VBA

Mohammednt0
4StarLounger
Posts: 456
Joined: 05 Dec 2016, 13:48

im not able to enter this formula using VBA

Post by 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

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: im not able to enter this formula using VBA

Post by 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
Best wishes,
Hans

Mohammednt0
4StarLounger
Posts: 456
Joined: 05 Dec 2016, 13:48

Re: im not able to enter this formula using VBA

Post by Mohammednt0 »

thanks it worked