Date type mismatch

[Patti]

Can someone pls tell me what I'm doing wrong?

Dim startDate As Date
Dim endDate As Date

startDate = Application.Min(Range("a:a"))
endDate = Application.Max(Range("a:a"))

Later, I try to use this in a different sheet I get type mismatch no matter
what I try:

Cells("Cl").Value = "Summary for " & startDate & "- " & endDate

I also found that I need to activate the sheet that has the dates because

With Sheets ("mySheet")
startDate = Application.Min(Range("a:a"))
endDate = Application.Max(Range("a:a"))
End With

is still trying set the dates from the active sheet.

Thanks in advance!

 

[Norman Jones]

Hi Patti,

Cells("Cl").Value

should be

Range("C1") or Cells(1, "C") or Cells(1,3)

Also, to refer to Sheets("mySheet"), inside your With ... End with statement

Application.Min(Range("a:a"))
and
Application.Max(Range("a:a"))

each need a period preceding Range("a:a")

Making these adjustments, this worked for me:

Sub Tester()
Dim startDate As Date
Dim endDate As Date

With Sheets("mySheet")
startDate = Application.Min(.Range("a:a"))
endDate = Application.Max(.Range("a:a"))
End With

ActiveSheet.Range("C1").Value = _
"Summary for " & startDate & "-" & endDate
End Sub

 

[Tom Ogilvy]

With Sheets ("mySheet")
startDate = Application.Min( .Range("a:a"))
endDate = Application.Max( .Range("a:a"))
End With

Put a period in front of Range and you won't have to select the sheet.
Otherwise, using the With construct does nothing.

I don't know why you are getting a type mistmatch. Place

Option Explicit

at the top of all your modules and see if the routine where the error occurs
can see your variables (startDate and endDate)

I don't know where they are declared, but if at the top of a module you
might declare them as

Public startDate As Date
Public endDate As Date

 

[Tom Ogilvy]

Missed that one. Good Catch.

 

[Patti]

Thanks Tom & Norman - you guys are the best!

Patti