How to handel error '1004' for Vlookup in a VBA code.

**sktneer** · 09-29-2013, 07:55 AM

Hello Forum members,

I am trying to delete a row from a sheet sh2 for a value in column A which is not found in column A of sheet sh1.
The code runs until For i=3 because the first three values in column A of sh2 sheet are already there in sh1 sheet. But the code gives an error For i=4 because the value in row 4 and column A of sh2 sheet is not there in sh1 sheet. And in this case I want to delete row 4 of sh2 sheet. I am not sure how to handle this. Please assist me. The error I get is as follows...

Error: "Run-time error '1004': Unable to get the Vlookup property of the WorksheetFunction class.

Please Login or Register  to view this content.

**HaHoBe** · 09-29-2013, 08:09 AM

Hi, sktneer,

maybe just use WorksheetFunction.CountIf(RangeToSearch, ItemToSearch) to return either 0 (not found) or 1 or greater (found) before deleting. And when deleting I would work from the bottom up using Step - 1.

Ciao,
Holger

**sktneer** · 09-29-2013, 09:41 AM

Originally Posted by HaHoBe

Hi, sktneer,

maybe just use WorksheetFunction.CountIf(RangeToSearch, ItemToSearch) to return either 0 (not found) or 1 or greater (found) before deleting. And when deleting I would work from the bottom up using Step - 1.

Ciao,
Holger

Thanks a lot HaHoBe !
It worked. You reminded me of using Step -1 , I was making mistake at that point too.
Thanks indeed for your help.

Though I am not marking this thread as solved. As I want to see if somebody can correct me in the original code itself. I just want to know that how can I use Vlookup function to achieve this.

**Norie** · 09-29-2013, 10:00 AM

VLOOKUP isn't really the appropriate function to use if you want to find a match in a column.

MATCH would be more appropriate.

Anyway, I've kept the VLOOKUP in the following so give it a try

Please Login or Register  to view this content.

If you wanted to use MATCH.

Please Login or Register  to view this content.

**HaHoBe** · 09-29-2013, 10:04 AM

Hi, sktneer,

another way might be:

Please Login or Register  to view this content.

Ciao,
Holger

**sktneer** · 09-29-2013, 11:40 AM

Originally Posted by Norie

VLOOKUP isn't really the appropriate function to use if you want to find a match in a column.

MATCH would be more appropriate.

Anyway, I've kept the VLOOKUP in the following so give it a try

Please Login or Register  to view this content.

If you wanted to use MATCH.

Please Login or Register  to view this content.

Thanks a lot Norie ! It really helped me to understand the concept well and to know what wrong I was doing. Your both codes worked perfectly.
Thanks once again for helping me.

How to handel error '1004' for Vlookup in a VBA code.

LinkBack

Thread Tools

Rate This Thread

Display

How to handel error '1004' for Vlookup in a VBA code.

Re: How to handel error '1004' for Vlookup in a VBA code.

Re: How to handel error '1004' for Vlookup in a VBA code.

Re: How to handel error '1004' for Vlookup in a VBA code.

Re: How to handel error '1004' for Vlookup in a VBA code.

Thread Information

Users Browsing this Thread

Similar Threads

Need Help with 1004 Error Code

I get a 400 error or 1004 error depending on where I run the code

[SOLVED] Getting Error 1004 Object or Application Defined Error - Code to rearrange data

[SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004

Getting error 1004 on this code why?

Bookmarks

Bookmarks

Posting Permissions