Using ComboBoxes to get range of values
 

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

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