K
Kirk Lewis
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
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