Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Newbie VLOOKUP with dates question

  Asked By: Glenn    Date: Dec 16    Category: MS Office    Views: 732
  

I would appreciate any help or ideas with this. I receive an error
2042 or "Unable to get the VLOOKUP property of the WorksheetFunction
Class" when I use a VLOOKUP statement in VBA. I have an array with
dates in column 1 and numbers in columns 3 to 8. I would like to be
able to enter a date and search for a number in that row. My way of
doing this is to read each number in the row and test it. When I put
a Watch on myRange, I do not see a range so it may be that something
is wrong with the range but I can't see what it is.

Here is the statement:

Dim col As Integer
Dim Result As Variant
Dim startDate As Date
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A2:D10")


startDate = CDate(InputBox("Please enter Start Date: ", "START DATE"))

For col = 3 To 8

Result = Application.VLookup(startDate, myRange, col, False)
......
......


Next

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Ludo Ricci     Answered On: Dec 16

could it be because the range("A2:D10") is only 4 columns  wide and
you're looking at columns 3 to 8?

 
Answer #2    Answered By: Luigi Fischer     Answered On: Dec 16

I corrected the range  but the same error occurs. I
created a smaller array  and module to try and isolate the problem and
set the range wrongly at that point. When I step through the module,
the error occurs on the first pass i.e. when col=3.

 
Answer #3    Answered By: Latasha Wilson     Answered On: Dec 16

OK. Try changing:
Dim startDate As Date
to:
Dim startDate As Long

and changing;
startDate = CDate(InputBox("Please enter  Start Date: ", "STARTDATE"))
to:
startDate = DateValue(InputBox("Please enter start  Date:", STARTDATE"))

I'll take a guess that vlookup  doesn't like to handle the Date
variant; from excel help:
"Lookup_value can be a value, a reference, or a text string"

and include some more code to check for a valid date  input by the user.

 
Answer #4    Answered By: Ora Hanson     Answered On: Dec 16

The problem is occurring because the dates  aren't being matched. I
decided to make the column  and all references to dates as Strings. It
works fine.

 
Didn't find what you were looking for? Find more on Newbie VLOOKUP with dates question Or get search suggestion and latest updates.




Tagged: