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?

 

[xenou]

I don't use worksheet formulas in vba much (for no particular reason good or bad). So I don't have any practical experience with this error you are getting.

I can suggest an alternative approach, assuming you have gotten a valid date into your date variable st_date. We can use the find method of a range instead. I found this to work even though dates were formatted differently than the default date format. If the value is found, we get the cell as a range returned - so we check for whether a range was successfully set or not to see if we succeeded.

Dim rngFound As Range
Set rngFound = Range("J2:AAA2").Find(What:=st_date, LookIn:=xlFormulas)

If Not rngFound Is Nothing Then
    MsgBox rngFound.Address
Else
    MsgBox "Date was Not Found."
End If

Note: Incidentally, when using xlValues rather than xlFormulas in this code snippet, formatting did matter - the value in the cell as formatted had to be the vba default format of m/d/yyyy