Date Match Problem

[sodabrab]

Hey all,

I am trying to find the column number where a certain date is located using the match function but I am getting errors.

My code:

Dim sDate, As Long
Dim st As Date

st = formMissingTime.txtStartDate.Value
sDate = Application.Match(st, Range("J2:AAA2"), 0)
Range(Columns(sDate)).Select

txtStartDate is a text box in my form "formMissingTime". I am getting a type mismatch with the last line of code, but I haven't been able to get the stuff above to sucessfully work either. Does anyone know what is going on?

 

[VoG]

Try

Dim sDate As Long
Dim st As Date

st = DateValue(formMissingTime.txtStartDate.Value)
sDate = Application.Match(st, Range("J2:AAA2"), 0)
Columns(sDate).Select

 

[sodabrab]

Sorry. In an effort to clean up my code before I posted, I removed to much on accident. Here is the actual code that is giving me a mismatch error:

Dim sDate, eDate As Long
Dim st, en As String

st = formMissingTime.txtStartDate.Value

sDate = Application.Match(st, Range("J2:AAA2"), 0)

Range(Columns(sDate), Columns(lastCol.Column - 13)).Select  'this line is giving the error
Selection.Delete Shift:=xlToLeft

I haven't done anything with eDate or en yet. I did a WATCH on the sDate variable and before I get a mismatch error, the value of sDate is Error 2042.

 

[VoG]

What is LastCol?

 

[xenou]

You could try converting textbox value to a date too. Dates are tricky and always more work - everything should be tested carefully as you write the code.

Dim st_text As String
Dim [COLOR="RoyalBlue"]st_date[/COLOR] As Date

st_text = formMissingTime.txtStartDate.Value
If IsDate(st_text) Then
    [COLOR="RoyalBlue"]st_date[/COLOR] = st_text
End if

sDate = Application.Match([COLOR="RoyalBlue"]st_date[/COLOR], Range("J2:AAA2"), 0)

Hopefully that succeeds.

 

[sodabrab]

LastCol is an integer which represents the last used column in the table.

I tried xenou's suggestion, but it is not finding the date in the table. When I hover over the variable st_date, its value is 12:00:00AM but it should have been the date I chose in my form which was 11/8/2010. sDate's value is still Error 2042 which makes sense. It won't find a time on my table.

Match is supposed to return an integer which represents the column it finds the date in right?

 

[VoG]

The textbox contains text so you'll need to convert it. Try

st = DateValue(formMissingTime.txtStartDate.Value)

MATCH returns effectively an offset, so if the date was in column J, MATCH would return 1. You'll need to adjust the code to account for that.

 

[sodabrab]

Also, I put a WATCH on formMissingTime.txtStartDate and it's value is blank ("") This is odd, because the calendar in my form populates the txtStartDate textbox fine.

 

[VoG]

Why don't you take the date value directly from the calendar - that should return a date rather than text.

 

[sodabrab]

st_date = DateValue(formMissingTime.txtStartDate.Value)
Dim st_date As Date

st_date = DateValue(formMissingTime.txtStartDate.Value)
sDate = Application.Match((st_date), Range("J2:AAA2"), 0)
 If IsError(sDate) = True Then
    MsgBox "Could not find start date in table"
End If

st_date now contains "11/8/2010" but my msgbox is popping up as sDate is still error 2042. How do I know if my dates in my table are formated as dates? The dates views look like 11/8 but when I click on the cell the real value is "11/8/2010"


The reason I wasn't taking the date from the calendar (aside from the fact I didn't really think about doing that) was because the user is filling out a form with a name txt box, start date, and end date. then they click a run button and the run button calls my macro in module1