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

question610

New Member
Joined
Jul 3, 2017
Messages
29
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:
Code:
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.

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

Code:
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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"")"
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top