Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totally confused - please help! | Excel Worksheet Functions | |||
Totally Lost | Excel Programming | |||
Totally wierd | Excel Worksheet Functions | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming |