Sorting error

[Daminc]

I copy and paste numerical order numbers from the web onto a spreadshee
and sort them.

I usually use a Macro to do this but recently the numbers don't sor
properly anymore.

It seems like some of these numbers are actually text but even after
format the cells to 'Number' they still do not sort.

I believe I've copied over some format or other with the number bu
even copy / paste special values doesn't seem to help.

Can anyone tell me what the score is here.

Below is an example of the numbers I'm trying to sort:


90721
90728
90731
90826
90851
90872
1002894
90709
90738
90851
90878
9090

 

[Chip Pearson]

Try the following to convert text to number. Enter 1 in some
empty cell and copy it. Then select your range of data. Go to the
Edit menu, choose Paste Special and select the Multiply
operation. Click OK.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Daminc" <[email protected]>
wrote in message
news[email protected]...

 

[Daminc]

I found this:


Code
-------------------
Sub ConvertToNumbers()
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(1, 1).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
With Selection
.VerticalAlignment = xlTop
.WrapText = False
End With
Selection.EntireColumn.AutoFit
End Su
-------------------


which seems like it clears it up but I don't know why.

Any advice

 

[Daminc]

Hi Chip, thanks for the advice.

your solution worked. For me to use it in a macro I'll have to put i
the value 1 in a cell somewhere and then clear it after th
transformation has been completed.

After looking at your solution the one I posted makes a little mor
sense but it failed (my one) when I tried to run it a second time whic
is odd

 

[Daminc]

I thought I'd put in the completed solution here in case it might help
someone else:



Code:
--------------------
Sub find_duplicates()
'
' find_duplicates Macro
' Macro recorded 07/07/2005 by PreeceJ
'

'
Application.Run "'Validation sheet.xls'!sort_for_duplicates"
Range("K1").Select
Application.Run "'Validation sheet.xls'!FindDups"
End Sub



Sub sort_for_duplicates()
'
' sort_for_duplicates Macro
' Macro recorded 07/07/2005 by PreeceJ
'

Range("K:K").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("'Validation sheet.xls'!saleID").Select

'The saleID range I've put in as a dynamic range on the excel sheet
'Insert>Name>Define
'Type in the name of the range (in this case saleID)
'In the box "Refers to" type in
' =OFFSET(worksheetname!$K$1,0,0,COUNTA(worksheetname!$K$1:$K$65),1)
'(change the name and range to suit your needs)

Application.Run "'Validation sheet.xls'!ConvertToNumbers"

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

End Sub


Sub ConvertToNumbers()

Range("n1") = 1
Range("n1").Copy
Selection.PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("n1") = ""
With Selection
.VerticalAlignment = xlTop
.WrapText = False
End With

End Sub


Sub FindDups()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub