+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,528

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

    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.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,000

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

    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
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,528

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

    Quote Originally Posted by HaHoBe View Post
    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.
    Last edited by sktneer; 09-29-2013 at 09:51 AM.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,300
    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.
    If posting code please use code tags, see here.

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    8,000

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

    Hi, sktneer,

    another way might be:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,528

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

    Quote Originally Posted by Norie View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need Help with 1004 Error Code
    By odoualex in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-09-2013, 12:12 PM
  2. I get a 400 error or 1004 error depending on where I run the code
    By mouseman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2013, 10:38 AM
  3. [SOLVED] Getting Error 1004 Object or Application Defined Error - Code to rearrange data
    By BlazzedTroll in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-10-2013, 12:10 PM
  4. [SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004
    By charizzardd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2012, 03:34 PM
  5. Getting error 1004 on this code why?
    By evilrtc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-10-2008, 07:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1