Sort Failing on Some Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following failed to sort just two rows (in over 1600) in the data:-

Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("E1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

I identified the two rows where the data in D and E looked exactly the same
but had remained separated by the sort. Simply typing over the data in E1
resulted in sort working correctly - both were in general format - and the
data was a simple number i.e. 21100. There where links in the sheet but these
weren't linked cells nor were they the result of any calculation / formula.
Preceding the Sort statement above with :-

For Each c In ActiveSheet.Range("A1:V" & LastInRow)
c = c.Value
Next c

Also fixed the problem. Has anybody ever come across anything like this ?? -
it's well weird and if I'd not seen it myself I'd find it hard to believe.
 
Did you notice if the figures in the unsorted cells were entered with
leading apostrophes? That forces Excel to treat the entries as text
regardless of the cells' underlying format.

Re-entering a number without the apostrophe would fix the problem.
 
Hi Mark, Yes I thought of that and no there were no leading apostrophies. Now
another thing that I failed to say in the original text was that the data
sheet and the macro are in different workbooks. If I record a macro in the
'data' book, and play it back all works fine BUT if I cut and paste the code
into the macro in my 'macro' book the error I describe in the original
question occures. It's well weird and the only similar thing I've ever come
across was a 'mad' program error when a colleague and I in despiration
retyped a line of code that was critical to the processing and suddenly it
all worked fine even though we both swore blind it was exactly the same as we
had before! I you're at all interested I can mail you the macro and the data
book - just to prove I'm not seeing things. In practical terms of course the
problem is solved but it has led to more questions about Excel that I'll post
separately - I'm interested in Cells.Value and How to Avoid Rounding Errors.
 
I believe you, John. I've been using Excel and other apps long enough
to know that strange things happen. I've seen a couple of threads
similar to yours in the Excel groups. Heck, I started one myself!

One thing that occurs to me is that you mention recording macros.
Recorded macros are rather inflexible and that may be part of the
problem, especially if you're subsequently running them from a separate
workbook. Is there any reason you couldn't keep the macro in the
'data' book?

The good thing is that you found a workaround. That's what I like
about Excel: It may put up a fight, but there's usually some way of
making it do what you need.
 
Thanks for believing me Mark. I agree that recorded macro's are inflexible -
I generally only use them to make Excel give me a statement that'll work
which I then paste into my programmed macro and modify to work there.

In this case the macro is for use on many input sheets by several users. The
input sheet/book is a variable in the general program.

Thanks again for taking an interest - it's good to know I'm not the only one
who's hit a problem like this!
 
Back
Top