View Full Version : Solved: date type mismatch
av8tordude
02-29-2012, 09:17 AM
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
02-29-2012, 10:31 AM
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
02-29-2012, 11:41 AM
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
Private Sub UserForm_Activate()
date1 = Month(Date) & "/" & Day(Date) & "/" & Year(Range("B7"))
End Sub
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
02-29-2012, 12:46 PM
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...
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
Paul_Hossler
02-29-2012, 12:55 PM
I usually use DateSerial() to get a true date, but I don't know if that'll work with the rest of your code
Option Explicit
Private Sub UserForm_Activate()
Dim TheDate As Date
TheDate = DateSerial(Year(Range("B7")), Month(Date), Day(Now))
date1.Value = TheDate
End Sub
Paul
av8tordude
02-29-2012, 02:56 PM
Ok...here what I've discover as a possible solution...
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
How can i incorporate the above code into the red highlighted code?
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
av8tordude
02-29-2012, 08:19 PM
Any assistance?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.