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!

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


---
Regards,
Norman



"Patti" wrote in message
...
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!

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

--
Regards,
Tom Ogilvy

"Patti" wrote in message
...
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!

Missed that one. Good Catch.

--
Regards,
Tom Ogilvy

"Norman Jones" wrote in message
...
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


---
Regards,
Norman



"Patti" wrote in message
...
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!

Thanks Tom & Norman - you guys are the best!

Patti


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Patti" wrote in message
...
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!