Sort function not behaving as expected

  • Thread starter Thread starter simal
  • Start date Start date
S

simal

The following list of numbers has been sorted in ascending format
Clearly Excel is considering the first two numbers as if the leadin
100 was missing. This is casuing havoc in a spreadsheet I use. The cel
format is 'number' and there aren't any leading spaces or other tex
anomalies. All the numbers have to have 7 digits, hence the leading
in a few of them. Does anyone know what's going on?

1004571
1004765
0215068
0215111
0215112
0215120
0215128
0585239
1000449
1002154
1002219
1002248

Regards,

S
 
Sorry, forgot to say.

I've written a macro which asks the user to enter company projec
numbers, amongst other information, into a dialogue box. This is the
'written' to a worksheet. I understand from other people that jus
because the cell format is 'number' that doesn't mean the content o
the cell will behave like a number. This may be a possible cause. Wha
do you think?

S
 
Hi
yes this seems to be the cause. You may post the relevant part of your
macro to check this
 
Hi,

I have attached what I hope is all the relevant macro.

ProjNumBox, Child1Box & Child2Box are userform text boxes. Most of wha
is below is checks to ensure that the format of the number typed in b
the user is correct. Apologies for what probably seems like amatue
programming by most peoples standards!

Some background history: The numbers in the original list I posted ar
project numbers. They used to be six digits and start with a 2. Afte
an Accounts software update they are now 7 digits and start with a 1
This means that the old numbers still in use have had a leading zer
applied to them. What I didn't show in the original list was th
project numbers that also have a child number attached. They come i
the form *******-***-*** and so are rather difficult to work wit
numerically. I have been assured that I can drop these in the ne
version I am writing and so I haven't included them here. If it is no
possible to deal with a leading zero or a number that has child number
separated by a "-" then I'll have to find an alternative method. Oddl
enough I seem to be able to get Excel to work on one or the other bu
rarely both. The child numbers don't seem to bother it so long as the
are NOT numerical. However, if the list is considered as text then th
leading zeroes become a problem.

Many thanks for offering your time.

S.

Code starts here:

Private Sub ProjNumBox_AfterUpdate()
If IsNumeric(ProjNumBox.Value) = False And ProjNumBox.Value <> "" Then
Response = MsgBox("The parent project number is non-numeric", vbOKOnly
_
"Incorrect Information")
ProjNumBox.Text = ""
ElseIf Len(ProjNumBox) <> 7 And ProjNumBox.Value <> "" Then
Response = MsgBox("The parent project number is not 7 digits"
vbOKOnly, _
"Incorrect Information")
End If
End Sub

Private Sub Child1Box_AfterUpdate()
If IsNumeric(Child1Box.Value) = False And Child1Box.Value <> "" Then
Response = MsgBox("The 1st child project number is non-numeric"
vbOKOnly, _
"Incorrect Information")
Child1Box.Text = ""
ElseIf Child1Box.Value = 0 Then
Child1Box.Text = ""
Else
Child1Box.Value = Format(Child1Box.Value, "000")
End If
End Sub

Private Sub Child2Box_AfterUpdate()
If IsNumeric(Child2Box.Value) = False And Child2Box.Value <> "" Then
Response = MsgBox("The 2nd child project number is non-numeric"
vbOKOnly, _
"Incorrect Information")
Child2Box.Text = ""
ElseIf Child2Box.Value = 0 Then
Child2Box.Text = ""
Else
Child2Box.Value = Format(Child2Box.Value, "000")
End If
End Sub

NumRows = (WorksheetFunction.CountA(Worksheets("Orde
Cover").Columns("A"))) - 2
If Child1Box.Text = "" Then
PNum = ProjNumBox.Text
ElseIf Child2Box.Text = "" Then
PNum = ProjNumBox.Text & "-" & Child1Box.Text
Else
PNum = ProjNumBox.Text & "-" & Child1Box.Text & "-" & Child2Box.Text
End If
For Numcheck = 6 To NumRows + 5
If Worksheets("Order Cover").Cells(Numcheck, 5).Text = PNum Then
Response = MsgBox("The project number is already taken", vbOKOnly, _
"Incorrect Information")
Exit Sub
End If
Next Numcheck

Worksheets("Order Cover").Cells(CellNum, 5).Value = PNum

Code ends here
 
Back
Top