Using ComboBoxes to get range of values
Sub masterRep1Search()
Dim CB1 As Integer
Dim CB3 As Integer
Dim Msg, Title, Style, Response
CB1 = Worksheets("Master Filter").Range("D3").Value
CB3 = Worksheets("Master Filter").Range("I3").Value
'Error Message
If CB1 CB3 Then
Msg = "End Year has to be greater than or equal to the
beginning year."
Style = vbOKOnly + vbCritical
Title = "Selection Error"
Response = MsgBox(Msg, Style, Title)
Exit Sub
End If
'Pulling of the Data
Range("List").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"And"), CopyToRange:=Range("Des"), Unique:=False
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.ScrollColumn = 1
End Sub
Your criteria range would be
Year Year
="=2003" ="<=2005"
so all you need to do is redefine your critiera range and build the criteria
using you combobox selections.
if you don't have a year column, but a date column, then you would build
your dates like
"=" & """=" & format(dateserial(cb1,1,1),"mm/dd/yyyy") & """"
"=" & """<=" & format(dateserial(cb3,12,31),"mm/dd/yyyy") & """"
--
Regards,
Tom Ogilvy
"Kirk Lewis" wrote in message
om...
Hi,
I'm writting a sub that I had in an existing spreadsheet. Basically,
the sub would search how would be retiring in a certain year, and
display a list of these individuals, range called "Des". Problem now
is that H/R doesn't want just one year to be displayed, but a range of
years.
I've written out the error message, but dim'd values for the
ComboBoxes (CB1 & CB3) CB1 is the beginning year range, CB3 is the
ending year range.
I can't figure out how to pull a range of years over.
Range names are. "List" contains a list of years, (2004 - 2018) and
"And" contains various companies this division owns, if H/R wants to
get to company specific.
Does anyone have any ideas on how to write the code for a range of
years?
Thanks,
-K
Sub masterRep1Search()
Dim CB1 As Integer
Dim CB3 As Integer
Dim Msg, Title, Style, Response
CB1 = Worksheets("Master Filter").Range("D3").Value
CB3 = Worksheets("Master Filter").Range("I3").Value
'Error Message
If CB1 CB3 Then
Msg = "End Year has to be greater than or equal to the
beginning year."
Style = vbOKOnly + vbCritical
Title = "Selection Error"
Response = MsgBox(Msg, Style, Title)
Exit Sub
End If
'Pulling of the Data
Range("List").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"And"), CopyToRange:=Range("Des"), Unique:=False
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=1
ActiveWindow.ScrollColumn = 1
End Sub
|