Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Max and Min date in Column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Max and Min date in Column
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Max and Min date in Column
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine Max and Min date in Column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formual to determine if date falls on weekend, adjust date to Mond | Excel Discussion (Misc queries) | |||
Need a formula to determine if date falls within a date range then | Excel Worksheet Functions | |||
How to programatically determine the start date and end date of a graph | Charts and Charting in Excel | |||
Determine which column to use by date | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |