Vlookup assistance - getting Run-Time error '1004'

**dank_01** · 07-30-2012, 05:46 PM

Hello and thanks for taking the time to read this....and hopefully provide some guidance

I'm very new to writing Macros and am trying to use the Vlookup - this first attempt is just a test to see if I can get it to work.
I have the range of A2:D367 named as: Useme
Column A is a list of dates, starting A2 = 1/1/2012 through A367 = 12/31/12
Column D lists the actual volume of transactions for that day.

The goal (for this test) is to pick a date and have Excel return the volume for that date.

Here is what I have:

Please Login or Register  to view this content.

If I omit the whole Vlookup part, the rest of the macro does correctly identify the proper row that holds the correct volume.
With the Vlookup part added, I get the Run Time Error '1004'

Any assistance would be greatly appreciated -
Thank you in advance,
Dan

**TMS** · 07-30-2012, 05:50 PM

UseMe needs to be Range("UseMe")

Regards, TMS

**dank_01** · 07-30-2012, 05:59 PM

Thank you TMShucks - I changed the line to:

Please Login or Register  to view this content.

And I still get the same runtime error: 1004: Unable to get the Vlookup property of the WorksheetFunction class.

I did double-check the range A2:D367 has the correct name of: Useme

I've included a copy of the workbook I'm using.

**TMS** · 07-30-2012, 06:21 PM

?range("useme").Address
$A$2:$D$367

The VLOOKUP searches the leftmost column of the range and returns the value in the column designated, in this case, 4.

You request a date, take out the day from it and add 1 ... for example, using your suggested date 15/07/2012 gives DayNum 197 and RowToUse 198. Sadly, it (the VLOOKUP) is not going to find 198 in a column of dates.

You would need to use LOOKUP or INDEX/MATCH when the search column is not on the left hand side of the range

Regards, TMS

**dank_01** · 07-30-2012, 06:28 PM

Ahh - that makes sense. I'll try those options tomorrow. One question though - can I ignore the row number and have the Vlookup go just by the date?

**TMS** · 07-30-2012, 06:51 PM

Looking for a date in a range of dates would seem a reasonable approach. But I don't know why you were doing what you were doing ... it's your process.

If looking for a date gives you what you need, way to go.

Thanks for the rep.

Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save