Solved: date type mismatch

[av8tordude]

The code in the userform was working just fine, until today. I'm not sure why, but its giving me an error of type mismatch. If the date in cell B7 is change to a date in the year 2012, then it works fine, but in the years prior, it errors. Any ideas what is happening?

[av8tordude]

I just discovered an anomoly with cdate and 02/29/2012. Apparrently, it will cause a type mismatch error on the 29th. I changed my date to the 28th and it works just fine.

Any ideas how to resolve this?

[Paul_Hossler]

I don't think it was the CDate(), which you didn't want to use anyway since you're trying to put it in a text field


[vba]
Private Sub UserForm_Activate()
date1 = Month(Date) & "/" & Day(Date) & "/" & Year(Range("B7"))
End Sub
[/vba]

I could see maybe using Format() ??

Also, in your B7 you had 2011, which is not a leap year, so 2/29/2011 would fail


Paul

[av8tordude]

Actually, I need to evaluate it as a date because of other coding that will be referencing this date. The purpose of cell B7 is to illustrate the problem I was encountering.

is the rest of the code...

[vba]If Not IsDate(Cells(Cur_Row, 2)) Then
Cur_Row = Application.Max(11, Range("B" & Rows.Count).End(xlUp).Row)

If Cells(Cur_Row, 2) >= CDate(Month(Date) & "/" & Day(Date) & "/" & Year(Range("B7"))) Then
Date1 = CDate(Application.WorksheetFunction.Max(Range("B11:B250")) + 1)
Else
Date1 = CDate(Month(Date) & "/" & Day(Date) & "/" & Year(Range("B7")))
End If
end if[/vba]

[Paul_Hossler]

I usually use DateSerial() to get a true date, but I don't know if that'll work with the rest of your code


[vba]
Option Explicit
Private Sub UserForm_Activate()
Dim TheDate As Date

TheDate = DateSerial(Year(Range("B7")), Month(Date), Day(Now))

date1.Value = TheDate

End Sub

[/vba]

Paul

[av8tordude]

Ok...here what I've discover as a possible solution...

[vba]If (Year(Date) Mod 4) Then
date1 = CDate(Month(Date) & "/" & Day(Date) & "/" & Year(Range("B7")))
Else
date1 = CDate(Month(Date) & "/" & Day(Date - 1) & "/" & Year(Range("B7")))
End If
[/vba]

How can i incorporate the above code into the red highlighted code?

[vba]If Not IsDate(Cells(Cur_Row, 2)) Then
Cur_Row = Application.Max(11, Range("B" & Rows.Count).End(xlUp).Row)

If Cells(Cur_Row, 2) >= CDate(Month(Date) & "/" & Day(Date) & "/" & Year(Range("B7"))) Then
date1 = CDate(Application.WorksheetFunction.Max(Range("B11:B250")) + 1)
Else
date1 = CDate(Month(Date) & "/" & Day(Date) & "/" & Year(Range("B7")))
End If
End If[/vba]

[av8tordude]

Any assistance?