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
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