Run-time Error 1004: Unable to get the VLookup property of the WorksheetFunction class

**sridhar_neel** · 12-08-2012, 07:51 AM

My code is below:-

Please Login or Register  to view this content.

I am getting the error I mentioned in the subject. I am trying to fetch a value from the 7th column (i.e. column H) of "myRange" after matching the value of "SystemType" in the first column (i.e. column B). Current values of SystemType is of String data type and the value of Charge is of Integer.

I searched the web and found many mentions of this problem. But I could find only 2 solutions. In both cases the code writers were matching "date" data type. It seems there is a difference between excel and VB macro for that data type. The solutions suggested changing the "date" type to "Range" type and the macro worked after that.

But in my case it is not so. I am matching & fetching "variant" type data. I am using MS Excel 2010 & MS VBA 7. Thanks very much in advance.

**Norie** · 12-08-2012, 08:11 AM

Have you tried declaring SystemType as String?

**Kyle123** · 12-08-2012, 08:25 AM

You appear to not have declared I which sets the value of your systemtype, does systemtype actually have a value in your code? Secondly if it does, is the value actually in the range you're looking up from?

**sridhar_neel** · 12-09-2012, 11:36 AM

Hi Norie - Thanks very much. Yes, have tried defining SystemType as String. Same result - Error 1004.

Hi Kyle123 - Thanks very much. I had declared "I" as integer though I did not paste that particular "Dim" statament into the snippet I pasted in Excelforum thread. And yes, SystemType has a string value (B1, Y4, B7, Y3....etc are the values in different rows of myRange).

To all reading this thread -- The beauty is that the same VLookup works perfectly inside the excel worksheet. All looked up values are proper and further calculations based ont the looked up values are also correct in a worksheet. It just that it does not work in the VBA macro.

**Norie** · 12-09-2012, 12:25 PM

Can you attach a sample workbook?

**sridhar_neel** · 12-10-2012, 04:21 AM

Hi Nori, Thanx very much. Teh problem is solved. There was a bug in my code which made SystemType to have no value. Thanx for the time & effort.

Run-time Error 1004: Unable to get the VLookup property of the WorksheetFunction class

LinkBack

Thread Tools

Rate This Thread

Display

Run-time Error 1004: Unable to get the VLookup property of the WorksheetFunction class

Re: Run-time Error 1004: Unable to get the VLookup property of the WorksheetFunction class

Re: Run-time Error 1004: Unable to get the VLookup property of the WorksheetFunction class

Re: Run-time Error 1004: Unable to get the VLookup property of the WorksheetFunction class

Re: Run-time Error 1004: Unable to get the VLookup property of the WorksheetFunction class

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions