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

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

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

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

Repeat job
 

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

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

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

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

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

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

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

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