Help - Nested IF

[wjsdoh]

I have a worksheet that has a specific row-filtered list of 4 choices in
column A. If you select choice 1 from column A, choice 1 selects rows 5 thru
4831 only which is correct. There is a sort macro button in row 4 of each
column H thru S - each of the columns H thru S have been assigned a macro
that will sort the selected range of rows in descending rank for the specific
column and rows -- for choice 1, it will rank the rows 5 thru 4831.
I'd like to be able to select choice 2 and have the rows for choice 2 which
are 4832 thru 12678 selected. Then I'd like to be able to select one of the
sort macro buttons in row 4 of one of the columns H thru S and have the macro
sort only the selected rows for choice 2, rows 4832 thru 12678.
And the same for choice 3 which refers to rows 12679 thru 19886; and the
same for choice 4 which refers to rows 19887 thru 26741.

 

[Jim Jackson]

Let us see the code you have and we can have a better grip on what you are
attempting.

 

[wjsdoh]

The code below is from the macro button that I created. Basically, the
selection filter which was created from the "Create List" of the Data option
on the Main Menu. I have 4 items in the list: Cars, Trucks, SUVs, Vans.
When I select "Trucks" from the list, it selects the rows for the truck range
which are rows D5 thru T4320 and it hides all the other rows which is
correct. At this point I can click on any one of the macro buttons that I
have created in row 4 columns H - S and get a descending sort of all the row
data from D5 thru T4320;

Range("A4").Select
Selection.AutoFilter Field:=1, Criteria1:="Trucks"
Range("D5:T4320").Select
Selection.Sort Key1:=Range("H5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("H5").Select

End Sub
What I need is to be able to select Cars from the list and get the range of
rows D4321 thru T12678; or select SUVs and get the range of rows D12679 thru
T19886 etc. Please HELP!!!!

 

[Jim Jackson]

V = InputBox("Enter your choice. (Cars, Trucks, SUVs, Vans)")
Selection.AutoFilter Field:=1, Criteria1:= V

Range("D5:T20000").Select
Selection.Sort Key1:=Range("H5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("H5").Select

End Sub

 

[wjsdoh]

Jim,
Thank you for your help. I am afraid I did not do a very good job of
communicating part 2 of the issue. My apologies. Here goes:
The worksheet also has a macro button in column H thru T, each column refers
to the sales for a specific month, i.e., column H is Jan 2007, column I is
Dec 2006 and so on. There is also a macro associated with each of these
buttons, see code for the December 2006 button below:
Sub RankSept()
'
' RankSept Macro
'
'
Range("D5:T4320").Select
Selection.Sort Key1:=Range("L5"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("L5").Select
End Sub
As you can see from the code above, the range is the same as the initial
range for Trucks. What I would like for it to do is some sort of IF
procedure where -- if I chose Trucks as the filter, it would not only select
the rows D5:T4320 for the column A filter drop-down box, but it would also
use the same appropriate row range selection to use for the sort month
macros, which would be rows D5:T4320; if I chose Cars as the filter, it
would not only select the rows D4320:T12668 for the column A filter drop-down
box, but it would also use the same appropriate row range selection to use
for the sort month macros, which in this case would be D4320:T12668; and rows
D12669:T19886 for the SUV selection and so on.

Something like:
If
Range("A4").Select
Selection.AutoFilter Field:=1, Criteria1:="Trucks"
Range("D5:T4320").Select
Else
If
Range("A4").Select
Selection.AutoFilter Field:=1, Criteria1:="Cars"
Range("D4321:T19881").Select
etc, etc, etc
That may not be the appropriate way to write it but hopefully that gives you
an idea of what I am trying to communicate. Can you help me with that?

Many thanks for all your efforts