Glaswegian
11-04-2014, 05:47 AM
Hi folks
I have a problem trying to filter between 2 dates using VBA.
Within my workbook, users have the opportunity to print data between the start of a month and the end of a month. Using a simple Userform, the user chooses a month and year and VBA does the rest - almost.
I capture the chosen month & year in separate cells on a hidden sheet - so if the user chooses 'August' and '2014' then the details are captured in 2 Named Ranges - 'PrintMonth' & 'PrintYear'. I then convert this using
=TEXT(PrintMonth &PrintYear,"dd/mm/yyyy")
and this allows me to then find the last date in the month using
=EOMONTH(M3,0)
which I convert to the same date format.
on the worksheet to be filtered, dates are in a Custom Date format - dd mmm yyyy
The code to filter and print is fairly straightforward (thanks to previous help from xld)
lngStart = Sheets("Totals").Range("M3").Value
lngEnd = Sheets("Totals").Range("M5").Value
With Sheets("Statements")
.AutoFilterMode = False
.Range("B9:B100").AutoFilter field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
If .Rows(10).Hidden = True Then
Set myRng = Sheets("Statements").Range("B9:B100").SpecialCells(xlCellTypeVisible).Areas(2).Cells(1, 11)
.Range("L9").Value = myRng.Offset(-1, 0).Value
Else
MsgBox "No data available for the selected period - please check.", vbOKOnly, "Warning"
Exit Sub
End If
'general print stuff here etc
Both variables are Dimmed as Long.
The code crashes on the lines setting the Long variable values, so I suspect this is to do with dates and formats, probably because of the way I'm converting the user choices. I've tried changing to US format dates but that did not help.
Any suggestions gratefully received..
I have a problem trying to filter between 2 dates using VBA.
Within my workbook, users have the opportunity to print data between the start of a month and the end of a month. Using a simple Userform, the user chooses a month and year and VBA does the rest - almost.
I capture the chosen month & year in separate cells on a hidden sheet - so if the user chooses 'August' and '2014' then the details are captured in 2 Named Ranges - 'PrintMonth' & 'PrintYear'. I then convert this using
=TEXT(PrintMonth &PrintYear,"dd/mm/yyyy")
and this allows me to then find the last date in the month using
=EOMONTH(M3,0)
which I convert to the same date format.
on the worksheet to be filtered, dates are in a Custom Date format - dd mmm yyyy
The code to filter and print is fairly straightforward (thanks to previous help from xld)
lngStart = Sheets("Totals").Range("M3").Value
lngEnd = Sheets("Totals").Range("M5").Value
With Sheets("Statements")
.AutoFilterMode = False
.Range("B9:B100").AutoFilter field:=1, Criteria1:=">=" & lngStart, Operator:=xlAnd, Criteria2:="<=" & lngEnd
If .Rows(10).Hidden = True Then
Set myRng = Sheets("Statements").Range("B9:B100").SpecialCells(xlCellTypeVisible).Areas(2).Cells(1, 11)
.Range("L9").Value = myRng.Offset(-1, 0).Value
Else
MsgBox "No data available for the selected period - please check.", vbOKOnly, "Warning"
Exit Sub
End If
'general print stuff here etc
Both variables are Dimmed as Long.
The code crashes on the lines setting the Long variable values, so I suspect this is to do with dates and formats, probably because of the way I'm converting the user choices. I've tried changing to US format dates but that did not help.
Any suggestions gratefully received..