WorksheetFunction.Match

[John P]

Hi. Can someone please pinpoint the error in this match function statement
using a textbox which contains the lookup date to match a column of dates in
column Z. Program works if I do not use the textbox otherwise it gives a
runtime error:

Set StartDate = Worksheets("Data1").Range("Z" &
WorksheetFunction.Match(DateValue(TextBox1.Value),
Worksheets("Data1").Range("Z3:Z600"), 0) + 2)

StartDate declared as a range object.
Dates are in 3/14/01 format.
Textbox1 contains a date
Column Z is a series of dates

When I replace the textbox with a cell to contain the lookup date, the rest
of the program works. So the error can be narrowed to this sub expression:
DateValue(TextBox1.Value)

Thanks for your advice.

 

[Jacob Skaria]

Try

Set StartDate = Worksheets("Data1").Range("Z" &
WorksheetFunction.Match(DateValue(TextBox1),
Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2)

If this post helps click Yes

 

[John P]

Still won't work, Jacob

Try

Set StartDate = Worksheets("Data1").Range("Z" &
WorksheetFunction.Match(DateValue(TextBox1),
Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2)

If this post helps click Yes

 

[Jacob Skaria]

Try

Dim dtTemp as Date
dtTemp = CDate(TextBox1.Text)
Set StartDate = Worksheets("Data1").Range("Z" & _
WorksheetFunction.Match(dtTemp, _
Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2)

OR try the below

Dim dtTemp as Date
dtTemp = CDate(TextBox1.Text)
Msgbox
WorksheetFunction.Match(dtTemp,Worksheets("Data1").Range("Z3:Z600").Value, 0)

If this post helps click Yes

 

[John P]

Using dtTemp = CDate(TextBox1.Text) still gives runtime error. So far, only
using a cell to contain the lookup date makes the program work:
Worksheets("Data1").Range("H2") = TextBox1.Value
I am perplexed as to why this is so.

 

[Jacob Skaria]

If no match is found MATCH will return an error; (like in worksheet formula).
To handle that.

Dim dtTemp as Date
Dim varTemp as variant
Dim rngTemp as range

dtTemp = CDate(TextBox1.Text)
Set rngTemp=Worksheets("Data1").Range("Z3:Z600")
varTemp=Application.match(dtTemp,rngTemp,0)
If IsError(varTemp) then
Msgbox "not found"
Else
Set StartDate = Worksheets("Data1").Range("Z" & varTemp + 2)
End if

If this post helps click Yes