Totally Totally baffled

[ordnance1]

My code below is triggered by a UserForm (when it places data into one of
the cells in rows 5 - 9) to sort names in the cells. It works great the
first and second time it runs but after that it only sorts rows 6 - 9. I am
at a total loss as to how to fix this. Any ideas?


bSELCTIONCHANGE = False

On Error GoTo ErrorHandler

Events.Disable_Events
Application.ScreenUpdating = False

Dim c As Range
Dim rowtop As Integer
Dim row1 As Integer
Dim Row5 As Integer


Set c = ActiveCell

If ActiveCell.Row < 10 Then
rowtop = 4
row1 = 5
Row5 = 9
End If


If Range("E" & rowtop).Value = Range("w1").Value Then

GoTo Continue2

End If

If Range("E" & rowtop).Value > "" Then
Range("D" & row1 & ":E" & Row5).Select
Selection.Sort Key1:=Range("D" & row1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End If

Continue2:


c.Select

ErrorHandler:

bSELCTIONCHANGE = True

Application.ScreenUpdating = True

Events.Enable_Events


End Sub

 

[Peter T]

Impossible to say without knowing anything about the code that precedes what
you have shown us.

FWIW looking at the snippet you did show us, if ActiveCell.Row >= 10 your
code wouldn't work.

Regards,
Peter T

 

[ordnance1]

That is all the code.

And it is ActiveCell.Row > 10 , not ActiveCell.Row >= 10 and that does work.

 

[p45cal]

ordnance1,
you never, ever, say 'thank-you'.

 

[Peter T]

Maybe you intended to post all the code but clearly you didn't.

Your code includes
If ActiveCell.Row < 10 Then
populate the variables rowtop etc

But if ActiveCell.Row >= 10 then
the variables are not populated so obviously the code will fail, at least it
would on the basis of the sample of code you posted.

However, we have no way of knowing if maybe you had already populated the
variables because you have not shown us your code, or maybe some other
problem.

Regards,
Peter T

 

[ordnance1]

I did fail to include the first 2 lines

Option Explicit

Sub SortOffice()

but beyond that it is the complete code and it works with the exception of
what I noted in the original post (the 1st and 2nd time the UserForm enters
a name it sorts all 5 rows correctly, after that it excludes the first row
when sorting). Now I did not include the UserForm code that places the data
in the cell and calls the SortOffice routine and have now included that
below.

To the more important issue, p45cal's comment. I have to admit he is mostly
right as of late. So I must apologies to you and the many others how have
provided invaluable assistance, not only on this question but many others.
It becomes easy in animosity to take for granted the assistance that one
receives. I do appreciate all the help, but then how would anyone know that
if I do not take the time to say Thanks. So I shall endeavor to do better.
I will begin here.

Thanks for the time and help you have provided it is appreciated.


Private Sub OK_Click()

ThisWorkbook.Activate
Application.ScreenUpdating = False
On Error GoTo Sub1

Selection.Value = ListBox1.List(ListBox1.ListIndex, 0)

Sub1:

SortCells.SortOffice

Range("A3").Select

ActiveWorkbook.Save

Application.ScreenUpdating = True
Unload EmployeeList

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

Sub SortOffice()

bSELCTIONCHANGE = False

On Error GoTo ErrorHandler

Events.Disable_Events
Application.ScreenUpdating = False

Dim c As Range
Dim rowtop As Integer
Dim row1 As Integer
Dim Row5 As Integer


Set c = ActiveCell

If ActiveCell.Row < 10 Then
rowtop = 4
row1 = 5
Row5 = 9
End If


If Range("E" & rowtop).Value = Range("w1").Value Then

GoTo Continue2

End If

If Range("E" & rowtop).Value "" Then
Range("D" & row1 & ":E" & Row5).Select
Selection.Sort Key1:=Range("D" & row1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

End If

Continue2:


c.Select

ErrorHandler:

bSELCTIONCHANGE = True

Application.ScreenUpdating = True

Events.Enable_Events


End Sub

 

[Peter T]

It might only have been 2 lines you didn't post but we had no way of
knowing.

Does the range include headers. If not change xlGuess to xlNo, that might be
the reason sometimes the first line does not get included in the sort.

In passing

a) If(?) the routine is only ever called from OK_Click it means activerow is
always 3, so why this If check
If ActiveCell.Row < 10 Then
However if the row is ever >= 10 the code will fail.

b) Best never declare variables intended to store row numbers As Integer. In
fact best not use As Integer at all, use As Long

c) Why the If blah = blah then goto Continue2
simply If blah <> blah Then .. dostuff

Regards,
Peter T

 

[ordnance1]

Wow thanks so much. It was changing xlGuess to xlNo that did the trick.

Oddly enough when I recorded a sort (with My data has headers, not checked)
to get an idea what the code should look like, the resulting code had
xlGuess, so I blindly followed suit.


So again

Thanks for your time and efforts

 

[ordnance1]

Thank you

I had started to take advantage of what was not mine to take advantage of.

Without the skills of so many people on in these groups so many like me
would still be producing simplistic code of not much value. I drive a
Specialized Transportation bus for a living, but have always loved dabbling
in Excel and with the help of many in this group have created a number of
very useful solutions for my friends at work.

 

[p45cal]

A very graceful reply.. Thank you.

I knew from clues in your first post in the thread (Header:=xlGues
and the rows 5-9/ rows 6-9 difference) the answer very likely lay in
change to Header:=xlNo, but I didn't feel inclined to help as I had ha
no response from you to my reply to your 'RowSource for my ListBox
thread, furthermore when I looked at other threads started by you, well
..you know what I found.

Now all that has changed and all is well.

regards,