I have a column containing date, well sort of, they are actually entered as
text (ie: '2009-Apr-30)

I need to determine the min and max date value in the column using vba

I created a routine which I thought would do the job but I keep getting a
Type mismatch error?

Dim iLastRow As Integer
Dim Mindt As Date
Dim Maxdt As Date
Dim Curdt As Date

SrcWks = "Données"
DesWks = "Rapport"

Worksheets(SrcWks).Select
Worksheets(SrcWks).Range("A1").Select
Selection.End(xlDown).Select 'Find the last row in the data series
iLastRow = ActiveWindow.RangeSelection.Row

'Determine Min and Max Dates to build the report on
Mindt = CDate(Worksheets(SrcWks).Cells(2, 5).Value)
Maxdt = Mindt
For x = 2 To iLastRow
Curdt = CDate(Worksheets(SrcWks).Cells(x, 5).Value)
If Curdt > Maxdt Then Maxdt = Curdt
If Curdt < Mindt Then Mindt = Curdt
Next x

What I find curious is the fact that if I set the Curdt to a set Cells Value
instead of variable on x the code works (doesn`t loop though, but does not
return an error), but when I use x to define Curdt, I get the error.
Anyways, Could someone show me the proper way to tackle this issue.

Thank you,

QB

You are over thinking it.

Worksheets(SrcWks).Range("A1").select
Selection.End(xlDown).Select
Maxdt = Application.Worksheetfunction.Max(Range("A1:" & Activecell.Address)
Mindt = Application.Worksheetfunction.Min(Range("A1:" & Activecell.Address)

VBA is aware of the builtin Excel Max/Min functions. So we just use those -
no looping required.

"QB" wrote:

> I have a column containing date, well sort of, they are actually entered as
> text (ie: '2009-Apr-30)
>
> I need to determine the min and max date value in the column using vba
>
> I created a routine which I thought would do the job but I keep getting a
> Type mismatch error?
>
> Dim iLastRow As Integer
> Dim Mindt As Date
> Dim Maxdt As Date
> Dim Curdt As Date
>
> SrcWks = "Données"
> DesWks = "Rapport"
>
> Worksheets(SrcWks).Select
> Worksheets(SrcWks).Range("A1").Select
> Selection.End(xlDown).Select 'Find the last row in the data series
> iLastRow = ActiveWindow.RangeSelection.Row
>
> 'Determine Min and Max Dates to build the report on
> Mindt = CDate(Worksheets(SrcWks).Cells(2, 5).Value)
> Maxdt = Mindt
> For x = 2 To iLastRow
> Curdt = CDate(Worksheets(SrcWks).Cells(x, 5).Value)
> If Curdt > Maxdt Then Maxdt = Curdt
> If Curdt < Mindt Then Mindt = Curdt
> Next x
>
> What I find curious is the fact that if I set the Curdt to a set Cells Value
> instead of variable on x the code works (doesn`t loop though, but does not
> return an error), but when I use x to define Curdt, I get the error.
> Anyways, Could someone show me the proper way to tackle this issue.
>
> Thank you,
>
> QB

I tried something similar at the very beginning, but as with your code it
returns 0? I'm not sure but I think it is because the dates are entered as
Text when exported from Access. I don't understand why Access does this, but
it does.

If you have any further ideas, please let me know. Your help is greatly
appreciated!

QB





"BobT" wrote:

> You are over thinking it.
>
> Worksheets(SrcWks).Range("A1").select
> Selection.End(xlDown).Select
> Maxdt = Application.Worksheetfunction.Max(Range("A1:" & Activecell.Address)
> Mindt = Application.Worksheetfunction.Min(Range("A1:" & Activecell.Address)
>
> VBA is aware of the builtin Excel Max/Min functions. So we just use those -
> no looping required.
>
> "QB" wrote:
>
> > I have a column containing date, well sort of, they are actually entered as
> > text (ie: '2009-Apr-30)
> >
> > I need to determine the min and max date value in the column using vba
> >
> > I created a routine which I thought would do the job but I keep getting a
> > Type mismatch error?
> >
> > Dim iLastRow As Integer
> > Dim Mindt As Date
> > Dim Maxdt As Date
> > Dim Curdt As Date
> >
> > SrcWks = "Données"
> > DesWks = "Rapport"
> >
> > Worksheets(SrcWks).Select
> > Worksheets(SrcWks).Range("A1").Select
> > Selection.End(xlDown).Select 'Find the last row in the data series
> > iLastRow = ActiveWindow.RangeSelection.Row
> >
> > 'Determine Min and Max Dates to build the report on
> > Mindt = CDate(Worksheets(SrcWks).Cells(2, 5).Value)
> > Maxdt = Mindt
> > For x = 2 To iLastRow
> > Curdt = CDate(Worksheets(SrcWks).Cells(x, 5).Value)
> > If Curdt > Maxdt Then Maxdt = Curdt
> > If Curdt < Mindt Then Mindt = Curdt
> > Next x
> >
> > What I find curious is the fact that if I set the Curdt to a set Cells Value
> > instead of variable on x the code works (doesn`t loop though, but does not
> > return an error), but when I use x to define Curdt, I get the error.
> > Anyways, Could someone show me the proper way to tackle this issue.
> >
> > Thank you,
> >
> > QB

Try using Worksheetfuction itself as below. The below code is only for test
purpose on the activesheet. Pelase refer the worksheet correctly. Hope you
will try and feedback

Dim dtMin As Date
Dim dtMax As Date
Dim lngLastRow as Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
dtMin = WorksheetFunction.Min(Range("A1:A" & lngLastRow))
dtMax = WorksheetFunction.Max(Range("A1:A" & lngLastRow))

If this post helps click Yes
---------------
Jacob Skaria


"QB" wrote:

> I have a column containing date, well sort of, they are actually entered as
> text (ie: '2009-Apr-30)
>
> I need to determine the min and max date value in the column using vba
>
> I created a routine which I thought would do the job but I keep getting a
> Type mismatch error?
>
> Dim iLastRow As Integer
> Dim Mindt As Date
> Dim Maxdt As Date
> Dim Curdt As Date
>
> SrcWks = "Données"
> DesWks = "Rapport"
>
> Worksheets(SrcWks).Select
> Worksheets(SrcWks).Range("A1").Select
> Selection.End(xlDown).Select 'Find the last row in the data series
> iLastRow = ActiveWindow.RangeSelection.Row
>
> 'Determine Min and Max Dates to build the report on
> Mindt = CDate(Worksheets(SrcWks).Cells(2, 5).Value)
> Maxdt = Mindt
> For x = 2 To iLastRow
> Curdt = CDate(Worksheets(SrcWks).Cells(x, 5).Value)
> If Curdt > Maxdt Then Maxdt = Curdt
> If Curdt < Mindt Then Mindt = Curdt
> Next x
>
> What I find curious is the fact that if I set the Curdt to a set Cells Value
> instead of variable on x the code works (doesn`t loop though, but does not
> return an error), but when I use x to define Curdt, I get the error.
> Anyways, Could someone show me the proper way to tackle this issue.
>
> Thank you,
>
> QB