Repeat job

  • Thread starter Thread starter Jw
  • Start date Start date
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
 
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)
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
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)
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
For header, the initial setting in the sort dialog represents Excel's guess.
So the option in the code seems appropriate.
 
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)
 
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.
 
Back
Top