Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSortTextAsNumbers inconsistent? Excel 2003
Hi All,
I am getting inconsistent behaviour on data between recording and running a macro. If I record a macro that selects all data, then sorts by an ID number (in column A) and a date (in column B), I get asked the question about whether Excel should treat everything as a number even though it thinks some things are text, or sort numbers and text differently. I select treat all as number, and the sorting works correctly - in UK formatting, 13/05/2005 is before 02/06/2005 (assuming the same ID number). The code generated is: Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortTextAsNumbers However, if I RUN that code, the reverse is true - the "real date" field (the serialized number, 38xxx that is "true") seems to be used for the comparison. Am I missing something obvious that should go in the VBA code here? Many thanks, Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSortTextAsNumbers inconsistent? Excel 2003
Looks like this isn't the root of the problem - the root is that double
clicking on a csv has a different outcome than a Workbooks.Open(fileNameVariable) call. So I'm trying to figure out a way of opening within Excel that matches opening manually by double clicking - any ideas? Thanks, Dave "David Evans" wrote in message ... Hi All, I am getting inconsistent behaviour on data between recording and running a macro. If I record a macro that selects all data, then sorts by an ID number (in column A) and a date (in column B), I get asked the question about whether Excel should treat everything as a number even though it thinks some things are text, or sort numbers and text differently. I select treat all as number, and the sorting works correctly - in UK formatting, 13/05/2005 is before 02/06/2005 (assuming the same ID number). The code generated is: Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortTextAsNumbers However, if I RUN that code, the reverse is true - the "real date" field (the serialized number, 38xxx that is "true") seems to be used for the comparison. Am I missing something obvious that should go in the VBA code here? Many thanks, Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what is inconsistent formula in region in excel? | Excel Discussion (Misc queries) | |||
Dates display inconsistent in MS Excel 2007 | Charts and Charting in Excel | |||
Migrating Excel Data with inconsistent formats | Excel Discussion (Misc queries) | |||
Excel/Outlook Inconsistent results | Excel Programming | |||
Excel 97 - Inconsistent Change Events | Excel Programming |