Repeat job

J

Jw

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
 
B

Bob Phillips

Jim my,

As it seems a static sort, just record a macro to do it (Tools>Macro>Record
Macro)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
N

Norman Harker

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
B

Bob Phillips

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)
 
N

Norman Harker

Hi Bob!

I think the problem was that I didn't make a change to the Heading
setting when I recorded the sort. If I had, then the setting would
have been recorded. The other was possibly that I was using several
sorting routines. It was difficult to track why and when it guessed
wrong although I now suspect it was because I had data in the next but
one row above.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
T

Tom Ogilvy

For header, the initial setting in the sort dialog represents Excel's guess.
So the option in the code seems appropriate.
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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.
 
Top