Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
SUMPRODUCT or SUMIF if any values in a range equal any values in another range | Excel Worksheet Functions | |||
Search/Filter to find values in another range based on two cell values | Excel Programming | |||
Limiting values in comboboxes. | Excel Programming |