Totally Totally baffled
 

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

Totally Totally baffled
 

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" wrote in message
...
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

Totally Totally baffled
 

That is all the code.

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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

Totally Totally baffled
 

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196593

http://www.thecodecage.com/forumz

Totally Totally baffled
 

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" wrote in message
...
That is all the code.

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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

Totally Totally baffled
 

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

Totally Totally baffled
 

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" wrote in message
...
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

Totally Totally baffled
 

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

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

Totally Totally baffled
 

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" wrote in message
...

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=196593

http://www.thecodecage.com/forumz

Totally Totally baffled
 

A very graceful reply.. Thank you.

I knew from clues in your first post in the thread (Header:=xlGuess
and the rows 5-9/ rows 6-9 difference) the answer very likely lay in a
change to Header:=xlNo, but I didn't feel inclined to help as I had had
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,



ordnance1;703098 Wrote:

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" wrote in message
...

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


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread:
'Totally Totally baffled - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=196593)

'Microsoft Office Help - Microsoft Office Discussion - Excel VBA
Programming - Access Programming' (http://www.thecodecage.com/forumz)



--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196593

http://www.thecodecage.com/forumz