I want to a template in excel, by using macros, that has 10 data
categories, and then two list boxes, ex Sort by ___ then by ____ with
all 10 data categories in each that essentially lets me sort/filter
the data by the parameters specified in the list boxes. WOuld prefer
it if you could set it up the basics quickly in excel and email to me |
Request for Question Clarification by
maniac-ga
on
20 May 2004 15:41 PDT
Hello Gmoney111,
Let me make sure I understand what you are asking for before preparing a answer.
I assume
- your data is in a contiguous region (bounded by blank rows / columns).
- you have 10 columns of data (the categories).
- each set of data is on a single row (no multirow items).
Must the "List Boxes" be an in cell dropdown or can they be
implemented in some other way?
I assume you want to pick from a list of names but a dropdown list may
require you protect the sheet (which could impact other uses of the
sheet). I'll let you know if there is really a problem one way or
another after some research.
Final note - I can't email the completed spreadsheet; Google does not
allow the exchange of personal information to allow that. I will post
the completed macro with step by step instructions on how to put it
into your "template" for repeated use.
Please let us know if these assumptions / results are acceptable so we can proceed.
--Maniac
|
Clarification of Question by
gmoney111-ga
on
22 May 2004 07:54 PDT
Yes your answer is correct, thanks--let me know if you need more
info--I need this ASAP!
|
Clarification of Question by
gmoney111-ga
on
22 May 2004 17:00 PDT
Maniac-ga: Your assumptions are all correct-please let me know if you
have any other questions, I need this asap. FYI, I know how to record
macros, and that this will require VB macros, but please try to
explain as much as possible bc I dont know VB.
|
Hello Gmoney111,
Thank you for the clarification. I created a spreadsheet by performing
the following steps. There may be some slight differences in menu
selections / options / titles due to Excel version differences. If you
have ANY problems at all - be sure to make a clarification request so
I can explain more fully.
[for the next few steps, select the cell - enter the value shown]
A2 - Sort By
C2 - Then By
A4 through J4 - the names of the data categories / column headings
A5 through J## - the data to be sorted (must be contiguous)
Select B2 and then use menu
Data -> Validation...
In the first tab (Settings),
I selected List under "Allow",
entered =$A$4:$J$4 as the Source
made sure "ignore blank" and "in cell dropdown" were both selected
In the second tab (Input Message)
Title set to Sort By
Input Message set to Select a category
In the third tab (Error Alert)
left as defaults
Copy cell B2 and paste a copy into D2. You can optionally use Data ->
Validation... again to change the title to Then By if desired.
Use the menu
Tools -> Macro -> Visual Basic Editor
You should get a "project window" in the upper right and a list of the
open files. Select the file and then use menu
Insert -> Module
and then copy / paste the macro (at the end of the message) into the
module window that appeared. Use the menu
File -> Close and Return to Microsoft Excel
to go back to the worksheet.
At this point, I added a button named "Sort" near the top of the
worksheet. To do this, use the menu
View -> Toolbars -> Forms
to display the form toolbar. To add a Button, select the item that
looks like a button. On my system it is on the right side of the
second row, but you should be able to point at it and wait for a hint
from Excel indicating that this is the Button tool. Now drag a range
of cells in the worksheet to place the button. I did a two cell by two
cell button (pretty large - actually). You should get a window asking
which macro to assign to it - select SortByThenBy and then OK. At this
point, drag across the button name and change it to Sort (or something
more descriptive). Click outside the button to stop changing it.
At this point, you should be ready to go.
- Confirm that when you select either B2 or D2 that the dropdown list
appears with the categories specified.
- Confirm that after making these selections, the Sort button works
by sorting some sample data.
After those checks are done, I suggest removing the data and saving
the worksheet as your template.
Customizations:
If you move the selection cells, change the macro references to "B2"
and "D2" to match.
If you move the category cells, change the references to row 4 (three
places) in the Cells functions.
If you want to change category names - simply change their names in
the spreadsheet and the next time you select B2 or D2, the new list
will appear and the macro will work unchanged.
If you have an invalid entry in B2 or D2 - the macro will stop and
allow you to debug it. If you don't like this behavior, add lines like
SortBy = 1
ThenBy = 1
to change the values to default to the first column (if the value
doesn't match a category).
That covers all the cases I could think of for customization. If you
think of something else - don't hesitate to ask for a clarification so
I can address your needs.
--Maniac
--- SortByThenBy macro follows ---
Sub SortByThenBy()
'
' SortByThenBy Macro
' Macro recorded 5/24/2004 by Mark Johnson
'
' Capture the column names to sort by
SortBy1$ = Range("B2").Value
ThenBy2$ = Range("D2").Value
' Find the columns matching those names
For I = 1 To 10
If (Cells(4, I) = SortBy1$) Then SortBy = I
If (Cells(4, I) = ThenBy2$) Then ThenBy = I
Next I
Range("A4").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Cells(4, SortBy), Order1:=xlAscending,
Key2:=Cells(4, ThenBy) _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub |
Request for Answer Clarification by
gmoney111-ga
on
25 May 2004 06:04 PDT
Ok I followed the directions a few things:
1. When I click on the sort pulldowns, all I get is a little box that
shows "$a$4:$j$4"--I dont get the column headings/categories
2. The macro doesnt run--I cut and pasted it in excactly...it
breaks--this line is highlighted: Selection.Sort Key1:=Cells(4,
SortBy), Order1:=xlAscending,
Though I am not sure this is the precise problem with the macro, I
also got some type of syntax error...
|
Clarification of Answer by
maniac-ga
on
25 May 2004 10:37 PDT
Hello Gmoney111,
About the syntax error, it appears one of the lines was truncated when
I pasted in the answer. Replace
Selection.Sort Key1:=Cells(4, SortBy), Order1:=xlAscending,
Key2:=Cells(4, ThenBy) _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
with:
Selection.Sort Key1:=Cells(4, SortBy), Order1:=xlAscending, _
Key2:=Cells(4, ThenBy) , Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
[I'll check right after posting to confirm this reads correctly]
On the sort boxes, try the following steps:
Data -> Validation...
in the Allow box, delete what is there and then click in the
spreadsheet in cell A4 / drag across to J4. [you may have to move the
dialog box to do this] On my system, the dialog box shrinks to a small
region when you do the click / drag and then goes back to the normal
size when you release. This should set the correct value for the data
validation. Select OK to return to the spreadsheet. Check that the
dropdown now works and then repeat for the second sort box.
If these steps do not help - please let me know and I'll find some
place to upload my copy of the spreadsheet for you to access.
--Maniac
|
Request for Answer Clarification by
gmoney111-ga
on
26 May 2004 06:54 PDT
I cut and pasted that in, now I get "run-time error '1004'
application-defined or object-oriented error. This is very
frustrating. Maybe you can post all of the code for the macro on a
page somewhere and I can copy the entire thing in again?
|
Request for Answer Clarification by
gmoney111-ga
on
26 May 2004 06:57 PDT
Also, I could not fix the pulldown problem--my version of excel does
not allow my to "delete" what is in the Allow box--I have to choose
one of the options, so I cant click and drag in the row with the data
headings
|
Clarification of Answer by
maniac-ga
on
26 May 2004 10:20 PDT
Hello Gmoney111,
I was afraid something like this would happen - diagnosing a problem
like this is hard to do remotely. Download a copy of
http://homepage.mac.com/mhjohnson/maniac-ga/gmoney111/Workbook2.xls
to get a copy of the spreadsheet I prepared that includes:
- the macro already loaded into a module
- the dropdown lists are set up properly
- the Sort button is set up
- a sample set of categories are listed
- some sample data is included
That way - you can change the dropdown values, press Sort and see the
results on the screen.
You can also change the category names in row 4 and then select a
dropdown list to see the list of categories are updated there as well.
That should give you the right material to produce the template. Let
me know how it goes and I am truly sorry this did not work right for
you from the original instructions.
--Maniac
|
Request for Answer Clarification by
gmoney111-ga
on
26 May 2004 14:00 PDT
Ok that helped a lot--just two more things:
1. There were actually more column headings/data categories (12), and
I was unable to expand the range via the macro. Can you tell me how,
or simply take the heading below, + put into new sheet and upload:
Company Name*Ticker*Price PS*Industry*Market Cap($M)*TEV($M)*EBIT*EBIT
Cap*P/BV*Assets/Shs Equity*ADV(M)*Description
2. I have some formulas that validate imported data, for example, if
the data returns is negative (ex., N/A) or an error message, then make
the value 0, instead of giving an error message in the cell. So I have
this one formula below.
=IF(g14<0, 0, IF(F14<=0, "Negative TEV",IF(ISTEXT(F14),0, H14/F14)))
I would like to add to this formula so that if g14 is text (using the
"if istext function) it returns a 0 if not, then the rest of this
logic applies. The nesting of the parens is driving me crazy (i was
able to do it for f14 as you can see), can you please simply insert
the if is text test into the formula above and give it to me, thanks.
Also, there are no problems with the number of rows right--if I import
say 3,000 rows of data into these sheet, the sorting should still work
right, I dont have to mess with changing ranges etc?
|
Clarification of Answer by
maniac-ga
on
26 May 2004 16:22 PDT
Hello Gmoney111,
I made the changes you asked for and updated
http://homepage.mac.com/mhjohnson/maniac-ga/gmoney111/Workbook2.xls
to have the new information.
Also - look at cell A35 to see the revised function. I'll include a
copy here for reference with it split into a few pieces to make it
more understandable (and prevent wrapping). This assumes you merely
wanted to return zero if G14 has text.
=IF(ISTEXT(G14),0,
IF(G14<0, 0,
IF(F14<=0, "Negative TEV",
IF(ISTEXT(F14),0, H14/F14))))
Excel does try to highlight the parenthenses in color but the nesting
here is pretty deep.
About your question / concern about sorting - I am not aware of any
limitations in Excel on sorting as long as the data is contigous (no
blank lines). If you have blank lines, let me know. I have some ideas
how to fix the macro so it will automatically process all the data it
finds in the spreadsheet. Excel should sort up to 10's of thousands of
rows without a problem. It might take a while, but it should run OK.
--Maniac
PS: The steps used to update the spreadsheet.
- revise the FOR loop in the macro to go to column 12 (was 10)
- revise the Data -> Validation ... Source to go from $A$4 to $L$4 (was $J$4).
- while I was at it, revised the title / text of the pop up messages
- copied the columns provided into the category headers
- retested the sort button after adding additional data / revised one
column to confirm the two level sort was working correctly
- entered the function in A35 and made the corrections for that as well
|
