PDA

View Full Version : [SOLVED] Select given month in various years



MichaelATO
05-23-2005, 05:41 AM
I am having no end of trouble trying to find a solution to finding a given month within a range. I have a spreadsheet that consists of 30 to 50 columns, their number depending on what details a client submits. the row headers are in row 20. the main columns are A (client code), D (Prd_End_Dt). The other columns are the monetary values submitted for various items. In the main only 60 rows will be displayed as we only require to do statistical analysis on five years of data or less, depending on how long the client has been active. However this can grow. We use the date format of "dd/mm/yyyy" in the Prd_End_Dt column. I have defined this range of data as
DynaRange (=OFFSET(Database!$A$20:$AZ$65536,0,0,COUNT(Database!$A:$A)+1)
This range is the full length as we can use the same sheet to analyse a group of clients.
Requirements:
to be able to view, for up to the full five years, a group of months, which are the same month for all years. For instance: Have the staff member click on a button that will pop up an input box and then enter a month name, either short - Aug, or long - August or the equivalent month number ( 7 = July) and then have the autofilter option kick in and select the given month for any year, within the Prd_End_dt column and have the rows of data for all available months of August displayed (all other months are of course hidden).
I have tried coding this myself to no avail. the closest workable solution i came up with is the following:


Sub FindMonths()
'On Error GoTo errorHandler
Dim mDate As Integer
Dim startDate As Date
Dim endDate As Date
Dim stStartCriteria As String
Dim stEndCriteria As String
Dim Months
Dim Years
Dim Days
Dim i As Integer
Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
Years = Array(2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010)
Days = Array(28, 29, 30, 31)
mDate = Application.InputBox(prompt:= _
"Enter 1 to 12 corresponding to the Month", _
Title:="Month Selector", _
Type:=1)
If mDate = 0 Then End
startDate = DateSerial(Years, Months, Days)
endDate = DateSerial(Years, Months + 1, Days)
stStartCriteria = ">=" & startDate
stEndCriteria = "<=" & endDate
'stStartCriteria = Format(startDate, "mm/dd/yyyy")
'stEndCriteria = Format(endDate, "dd/mm/yyyy")
Range("Dynarange").AutoFilter Field:=4, Criteria1:=stStartCriteria
' Operator:=xlAnd, _
' Criteria2:=stEndCriteria
'errorHandler:
'MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending This Search.......Please try again", 48
End Sub

Would someone please assist by providing me with a solution using VBA.
I thank you in advance for any assistance that can be provided.
Cheers
Michael

Bob Phillips
05-23-2005, 06:24 AM
I am having no end of trouble trying to find a solution to finding a given month within a range.

this any good?


Option Explicit
Sub FindMonths()
Dim mDate
Dim Months1, months2
Dim iLastRow As Long
Dim i As Long
Dim fOK As Boolean
If Range("E20").Value = "Temp" Then
Columns("E:E").Delete
End If
Months1 = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
months2 = Array("", "January", "February", "March", "April", "May", "June", _
"July", "August", "September", "October", "November", "December")
mDate = InputBox(prompt:= _
"Enter a month number or name", _
Title:="Month Selector")
If IsNumeric(mDate) Then
If mDate = 0 Then
Exit Sub
End If
Else
fOK = False
For i = LBound(Months1) To UBound(Months1)
If Months1(i) = mDate Then
fOK = True
Exit For
End If
Next i
If Not fOK Then
For i = LBound(months2) To UBound(months2)
If months2(i) = mDate Then
fOK = True
Exit For
End If
Next
End If
If Not fOK Then
Exit Sub
Else
mDate = i
End If
End If
Columns("E:E").Insert
Range("E21").Formula = "=MONTH(D21)"
Range("E21").NumberFormat = "General"
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("E21").AutoFill Destination:=Range("E21:E" & iLastRow)
Range("E20:E" & iLastRow).AutoFilter Field:=1, Criteria1:=mDate
Range("E20").Value = "Temp"
End Sub

BlueCactus
05-23-2005, 07:41 AM
Hi MichealATO! Welcome to VBAX. I took the liberty of adding vba tags to your code to make it more readable - hope you don't mind!

BlueCactus

MichaelATO
05-23-2005, 01:38 PM
Blue Cactus - thanks for fixing up the readability of the code. I must seem to be an extremely boring person when I look at some of the names you programmers have chosen.

XLD - thanks for taking the time to provide a very userfriendly and workable solution.
The code works fine, however it seems to like to do the filter on the month number instead of also on the name. I have gotten around that by simply asking the user to input a number corresponding to the month at the input box prompt.
I have noticed too that when the cancel button is selected that the filter is applied.
Is this due to the code asking for a '0'?

Thanks again.
cheers
Michael

Bob Phillips
05-23-2005, 02:50 PM
XLD - thanks for taking the time to provide a very userfriendly and workable solution.
The code works fine, however it seems to like to do the filter on the month number instead of also on the name. I have gotten around that by simply asking the user to input a number corresponding to the month at the input box prompt.

No, I have coded it so that the user can input the month n umber, the short month name, or the long month name, and all will work. It does convert it to a month n umber and use that in the filter though.


I have noticed too that when the cancel button is selected that the filter is applied.
Is this due to the code asking for a '0'?

Yes that is it, I should test the cancel beforehand.

Here is an amended version, which handles the cancel, and also filters as input. See if you like this better.



Option Explicit

Sub FindMonths()
Dim mDate
Dim Months1, months2
Dim iLastRow As Long
Dim i As Long
Dim sFormula As String
Dim fOK As Boolean
If Range("E20").Value = "Temp" Then
Columns("E:E").Delete
End If
Months1 = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
months2 = Array("", "January", "February", "March", "April", "May", "June", _
"July", "August", "September", "October", "November", "December")
mDate = InputBox(prompt:= _
"Enter a month number or name", _
Title:="Month Selector")
If mDate = "" Then
Exit Sub
ElseIf IsNumeric(mDate) Then
If mDate = 0 Then
Exit Sub
End If
sFormula = "=MONTH(D21)"
Else
fOK = False
For i = LBound(Months1) To UBound(Months1)
If Months1(i) = mDate Then
sFormula = "=TEXT(D21,""mmm"")"
fOK = True
Exit For
End If
Next i
If Not fOK Then
For i = LBound(months2) To UBound(months2)
If months2(i) = mDate Then
sFormula = "=TEXT(D21,""mmmm"")"
fOK = True
Exit For
End If
Next
End If
End If
Columns("E:E").Insert
Range("E21").Formula = sFormula
Range("E21").NumberFormat = "General"
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("E21").AutoFill Destination:=Range("E21:E" & iLastRow)
Range("E20:E" & iLastRow).AutoFilter Field:=1, Criteria1:=mDate
Range("E20").Value = "Temp"
End Sub

MichaelATO
05-26-2005, 01:54 PM
Hi XLD:
the code works excellently.
Had just one aspect to add to the array's for the month names. Each array should contain the same details for the first character in each name in both upper and lower case - i.e. "March", "march". this negates having any problems with users.
I suppose the output could could be set to "proper" text case as well. I can fix that here.
If this is not done the filter would hide all values and not return any.

Thanks so much for your input.

cheers
Michael