| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat job
Hi, everyone
I daily used Excel 97 to sort the data by ascending from A5:Z99 by J5 (Delivery Date), how to write this code. Thanks Jimmy |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
Jim my,
As it seems a static sort, just record a macro to do it (ToolsMacroRecord Macro) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jw" wrote in message ... Hi, everyone I daily used Excel 97 to sort the data by ascending from A5:Z99 by J5 (Delivery Date), how to write this code. Thanks Jimmy |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
Hi Bob!
A minor point that can cause wailing and gnashing of teeth. I played with auto recording sorting the other day and wasn't impressed. The line that caused the problem was: Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I got variable results from sorting (I had quite a few different sorts) caused by the xlGuess. As usual with recording it is necessary to look through the code and in this case the approach necessary was to change xlGuess to xlNo. If the data does include the headings then xlYes is needed. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Bob Phillips" wrote in message ... Jim my, As it seems a static sort, just record a macro to do it (ToolsMacroRecord Macro) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jw" wrote in message ... Hi, everyone I daily used Excel 97 to sort the data by ascending from A5:Z99 by J5 (Delivery Date), how to write this code. Thanks Jimmy |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
Hi Norman,
Can't say I have experienced this problem myself. I rarely leave a recorded macro as it is, I usually change it, that being one of the obvious changes, making the range dynamic being another, but xlGuess has not caused me problems that I recall. That being said, I would agree with you that as selecting the Header option in the Sort dialog sets this to xlYes, changing it to xlNo is a wise move. I wonder why there is such an option? I could understand allowing one to manually code that option, but when you use the dialog you have to specify one or the other. Just MS I guess. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Norman Harker" wrote in message ... Hi Bob! A minor point that can cause wailing and gnashing of teeth. I played with auto recording sorting the other day and wasn't impressed. The line that caused the problem was: Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I got variable results from sorting (I had quite a few different sorts) caused by the xlGuess. As usual with recording it is necessary to look through the code and in this case the approach necessary was to change xlGuess to xlNo. If the data does include the headings then xlYes is needed. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Bob Phillips" wrote in message ... Jim my, As it seems a static sort, just record a macro to do it (ToolsMacroRecord Macro) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jw" wrote in message ... Hi, everyone I daily used Excel 97 to sort the data by ascending from A5:Z99 by J5 (Delivery Date), how to write this code. Thanks Jimmy |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
For header, the initial setting in the sort dialog represents Excel's guess.
So the option in the code seems appropriate. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Hi Norman, Can't say I have experienced this problem myself. I rarely leave a recorded macro as it is, I usually change it, that being one of the obvious changes, making the range dynamic being another, but xlGuess has not caused me problems that I recall. That being said, I would agree with you that as selecting the Header option in the Sort dialog sets this to xlYes, changing it to xlNo is a wise move. I wonder why there is such an option? I could understand allowing one to manually code that option, but when you use the dialog you have to specify one or the other. Just MS I guess. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Norman Harker" wrote in message ... Hi Bob! A minor point that can cause wailing and gnashing of teeth. I played with auto recording sorting the other day and wasn't impressed. The line that caused the problem was: Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I got variable results from sorting (I had quite a few different sorts) caused by the xlGuess. As usual with recording it is necessary to look through the code and in this case the approach necessary was to change xlGuess to xlNo. If the data does include the headings then xlYes is needed. -- Regards Norman Harker MVP (Excel) Sydney, Australia Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Bob Phillips" wrote in message ... Jim my, As it seems a static sort, just record a macro to do it (ToolsMacroRecord Macro) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jw" wrote in message ... Hi, everyone I daily used Excel 97 to sort the data by ascending from A5:Z99 by J5 (Delivery Date), how to write this code. Thanks Jimmy |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
Then it would be better to leave both option buttons not set.
Bob "Tom Ogilvy" wrote in message ... For header, the initial setting in the sort dialog represents Excel's guess. So the option in the code seems appropriate. -- Regards, Tom Ogilvy |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
Why? Excel usually make a good guess.
-- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Then it would be better to leave both option buttons not set. Bob "Tom Ogilvy" wrote in message ... For header, the initial setting in the sort dialog represents Excel's guess. So the option in the code seems appropriate. -- Regards, Tom Ogilvy |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
Because it is guessing, but by loading the no header option, it is
suggesting that there is no header. If both are left not set, it is clear that it is no positively known. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... Why? Excel usually make a good guess. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Then it would be better to leave both option buttons not set. Bob "Tom Ogilvy" wrote in message ... For header, the initial setting in the sort dialog represents Excel's guess. So the option in the code seems appropriate. -- Regards, Tom Ogilvy |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
And if it marks one, then probably worst case, It would be right 50% of the
time and I wouldn't have to change it. If I miss it and it is wrong, edit = undo. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Because it is guessing, but by loading the no header option, it is suggesting that there is no header. If both are left not set, it is clear that it is no positively known. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... Why? Excel usually make a good guess. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Then it would be better to leave both option buttons not set. Bob "Tom Ogilvy" wrote in message ... For header, the initial setting in the sort dialog represents Excel's guess. So the option in the code seems appropriate. -- Regards, Tom Ogilvy |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Repeat job
whatever .... "Tom Ogilvy" wrote in message ... And if it marks one, then probably worst case, It would be right 50% of the time and I wouldn't have to change it. If I miss it and it is wrong, edit = undo. -- Regards, Tom Ogilvy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Repeat | Excel Discussion (Misc queries) | |||
| Row to repeat while printing | Excel Discussion (Misc queries) | |||
| repeat function??? | Excel Worksheet Functions | |||
| how to repeat rows at the bottom while using the rows repeat at to | Setting up and Configuration of Excel | |||
| Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec | Excel Discussion (Misc queries) | |||
Linear Mode
