VBA- VLookup to display custom message when #N/A

[question610]

I have a vlookup as part of my macro where I through all the rows in the sheet and do a vlookup against another sheet in another workbook.
I was able to successfully get the vlookup to work through this method in VBA:

Report1.Range("D1:D" & LastRow).Formula = "=VLOOKUP(A1, " & TableRange & ", 6, False)"

I want it to show "Not Found" when the vlookup result is #N/A. I tried this directly on the excel sheet andthis worked. I need to put this in the VBA code so that it can be in my loop.

=IFERROR(VLOOKUP(A1, " & TableRange & ", 6, False),"Not Found)" (

However, I can't get the syntax right for excel vba to put =IFERROR(VLOOKUP(A1, " & TableRange & ", 6, False),"Not Found)" into each cell of D. I know it's something simple with the syntax, especially with the quotes that VBA does not like.

Report1.Range("D1:D" & LastRow).Formula = "=IFERROR(VLOOKUP(A1, " & TableRange & ", 6, False),"Not Found")"

and I get the error, "Expected end of statement" and it highlights ")" .

Also, I chose to do vlookup this way instead of the application.worksheetfunction.vlookup which does not work for me and I find that simply putting the formula into the cell is much simpler and I prefer this way

 

[Jonmo1]

Welcome to the board.

When entering formula using VBA, and that formula contains quote marks, you have to double up those quote marks.

Try
Report1.Range("D1:D" & LastRow).Formula = "=IFERROR(VLOOKUP(A1, " & TableRange & ", 6, False),""Not Found"")"

 

[question610]

Thank you! It worked!

 

[Jonmo1]

You're welcome.