Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
Hi there, I thought I posted a thread about this, but can't seem to find it.
Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
Did you read the replies to yesterday's post?
ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me
of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
You haven't shared many details, so I used:
With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With You'll want to change the name of the sheet with the list from Hidden to what you use. I used A1 through the last used cell in column A. You'll have to modify that to use the range you want. And the sort statement should be what you need, too. If this doesn't help, then post back with your modified code and more details. ATChurch wrote: No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
Thankyou for your time, but I had one of those wonderful "Ooh, I wonder..."
moments last night and it appears to be working now. I separated my one large macro into three smaller ones (convert to numbers, sort, convert to letters) then made a macro that just ran those three. I then set that macro to run when the wookbook was saved (as I had tried to do before) and it worked properly. So again, thanks for your time anyway. "Dave Peterson" wrote: You haven't shared many details, so I used: With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With You'll want to change the name of the sheet with the list from Hidden to what you use. I used A1 through the last used cell in column A. You'll have to modify that to use the range you want. And the sort statement should be what you need, too. If this doesn't help, then post back with your modified code and more details. ATChurch wrote: No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . -- Dave Peterson . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
Hope you're still getting these messages.
It seems I still can't get it to work once I email to someone else using my technique. I've tried your suggestion now with what I though to be my own variables: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) myArr = Array("High", "Medium", "Low") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Andrew Welsh", "Mark Watkins", "Steve Lane").Range("a:g") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub But it returns an error on the Set myRng "Compile error: wrong number of arguments or invalid property assignments." I've tried only listing one sheet and adjusting the cell range but Help just isn't explaining what's wrong or what I should be doing clear enough for me to figure it out. "Dave Peterson" wrote: You haven't shared many details, so I used: With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With You'll want to change the name of the sheet with the list from Hidden to what you use. I used A1 through the last used cell in column A. You'll have to modify that to use the range you want. And the sort statement should be what you need, too. If this doesn't help, then post back with your modified code and more details. ATChurch wrote: No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . -- Dave Peterson . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
Oh! The column which contains High/Medium/Low is G, incase that matters.
"ATChurch" wrote: Hope you're still getting these messages. It seems I still can't get it to work once I email to someone else using my technique. I've tried your suggestion now with what I though to be my own variables: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) myArr = Array("High", "Medium", "Low") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Andrew Welsh", "Mark Watkins", "Steve Lane").Range("a:g") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub But it returns an error on the Set myRng "Compile error: wrong number of arguments or invalid property assignments." I've tried only listing one sheet and adjusting the cell range but Help just isn't explaining what's wrong or what I should be doing clear enough for me to figure it out. "Dave Peterson" wrote: You haven't shared many details, so I used: With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With You'll want to change the name of the sheet with the list from Hidden to what you use. I used A1 through the last used cell in column A. You'll have to modify that to use the range you want. And the sort statement should be what you need, too. If this doesn't help, then post back with your modified code and more details. ATChurch wrote: No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . -- Dave Peterson . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
First, you removed the declarations from the procedure and the "Option Explicit"
line from the module. This is a bad idea. It makes errors much more likely and debugging a lottttttttttttt more difficult. Second, I wouldn't use the BeforeSave event. I'd either use a manual method (a button on the worksheet) or use the _Open event. I could be doing lots of intermediate saves and each would change the layout of my data--and I would find that irritating while I was editing my data. Third, a range is an object that exists on a single worksheet--but you can loop through each worksheet and do the work on that sheet. Option Explicit Private Sub Workbook_Open() Dim myArr As Variant Dim myRng As Range Dim WksNames As Variant Dim wCtr As Long Dim myListNumber As Long myArr = Array("High", "Medium", "Low") WksNames = Array("Andrew Welsh", _ "Mark Watkins", _ "Steve Lane") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) For wCtr = LBound(WksNames) To UBound(WksNames) Set myRng = Me.Worksheets(WksNames(wCtr)).Range("A:G") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Next wCtr Application.DeleteCustomList myListNumber End Sub (Untested, but it did compile.) ATChurch wrote: Hope you're still getting these messages. It seems I still can't get it to work once I email to someone else using my technique. I've tried your suggestion now with what I though to be my own variables: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) myArr = Array("High", "Medium", "Low") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Andrew Welsh", "Mark Watkins", "Steve Lane").Range("a:g") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub But it returns an error on the Set myRng "Compile error: wrong number of arguments or invalid property assignments." I've tried only listing one sheet and adjusting the cell range but Help just isn't explaining what's wrong or what I should be doing clear enough for me to figure it out. "Dave Peterson" wrote: You haven't shared many details, so I used: With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With You'll want to change the name of the sheet with the list from Hidden to what you use. I used A1 through the last used cell in column A. You'll have to modify that to use the range you want. And the sort statement should be what you need, too. If this doesn't help, then post back with your modified code and more details. ATChurch wrote: No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
Argh, already replied once, but it seems to have been lost to the ether.
I've tried your code and it sorted column A alphabetically. However I managed to get it working with: ..Cells.Sort key1:=.Columns("G"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 However, this is not the only thing I need to sort by. It also needs to sort by: Column E descending Column B Ascending With this in mind, I tried: ..Cells.Sort key1:=.Columns("G"), Order1:=xlAscending, _ Key2:=Columns("E"), Order2:=xlDescending, _ Key3:=Columns("B"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 But now I get a 1004 runtime error. I'm also sticking with the BeforeSave event as there is only going to be minor editing of existing data. The majority of changes will be the addition/deletion of entire rows followed by a print of the revised table. "Dave Peterson" wrote: First, you removed the declarations from the procedure and the "Option Explicit" line from the module. This is a bad idea. It makes errors much more likely and debugging a lottttttttttttt more difficult. Second, I wouldn't use the BeforeSave event. I'd either use a manual method (a button on the worksheet) or use the _Open event. I could be doing lots of intermediate saves and each would change the layout of my data--and I would find that irritating while I was editing my data. Third, a range is an object that exists on a single worksheet--but you can loop through each worksheet and do the work on that sheet. Option Explicit Private Sub Workbook_Open() Dim myArr As Variant Dim myRng As Range Dim WksNames As Variant Dim wCtr As Long Dim myListNumber As Long myArr = Array("High", "Medium", "Low") WksNames = Array("Andrew Welsh", _ "Mark Watkins", _ "Steve Lane") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) For wCtr = LBound(WksNames) To UBound(WksNames) Set myRng = Me.Worksheets(WksNames(wCtr)).Range("A:G") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Next wCtr Application.DeleteCustomList myListNumber End Sub (Untested, but it did compile.) ATChurch wrote: Hope you're still getting these messages. It seems I still can't get it to work once I email to someone else using my technique. I've tried your suggestion now with what I though to be my own variables: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) myArr = Array("High", "Medium", "Low") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Andrew Welsh", "Mark Watkins", "Steve Lane").Range("a:g") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub But it returns an error on the Set myRng "Compile error: wrong number of arguments or invalid property assignments." I've tried only listing one sheet and adjusting the cell range but Help just isn't explaining what's wrong or what I should be doing clear enough for me to figure it out. "Dave Peterson" wrote: You haven't shared many details, so I used: With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With You'll want to change the name of the sheet with the list from Hidden to what you use. I used A1 through the last used cell in column A. You'll have to modify that to use the range you want. And the sort statement should be what you need, too. If this doesn't help, then post back with your modified code and more details. ATChurch wrote: No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not working properly when run automatically
Notice the Key1:= keyword. It had a leading dot in front of Columns().
.Cells.Sort key1:=.Columns(7), Order1:=xlAscending, _ Key2:=.Columns(5), Order2:=xlDescending, _ Key3:=.Columns(2), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 That mean that those objects (ranges in this case) belong to the object (myRng) in the previous With statement. ATChurch wrote: Argh, already replied once, but it seems to have been lost to the ether. I've tried your code and it sorted column A alphabetically. However I managed to get it working with: .Cells.Sort key1:=.Columns("G"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 However, this is not the only thing I need to sort by. It also needs to sort by: Column E descending Column B Ascending With this in mind, I tried: .Cells.Sort key1:=.Columns("G"), Order1:=xlAscending, _ Key2:=Columns("E"), Order2:=xlDescending, _ Key3:=Columns("B"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 But now I get a 1004 runtime error. I'm also sticking with the BeforeSave event as there is only going to be minor editing of existing data. The majority of changes will be the addition/deletion of entire rows followed by a print of the revised table. "Dave Peterson" wrote: First, you removed the declarations from the procedure and the "Option Explicit" line from the module. This is a bad idea. It makes errors much more likely and debugging a lottttttttttttt more difficult. Second, I wouldn't use the BeforeSave event. I'd either use a manual method (a button on the worksheet) or use the _Open event. I could be doing lots of intermediate saves and each would change the layout of my data--and I would find that irritating while I was editing my data. Third, a range is an object that exists on a single worksheet--but you can loop through each worksheet and do the work on that sheet. Option Explicit Private Sub Workbook_Open() Dim myArr As Variant Dim myRng As Range Dim WksNames As Variant Dim wCtr As Long Dim myListNumber As Long myArr = Array("High", "Medium", "Low") WksNames = Array("Andrew Welsh", _ "Mark Watkins", _ "Steve Lane") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) For wCtr = LBound(WksNames) To UBound(WksNames) Set myRng = Me.Worksheets(WksNames(wCtr)).Range("A:G") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Next wCtr Application.DeleteCustomList myListNumber End Sub (Untested, but it did compile.) ATChurch wrote: Hope you're still getting these messages. It seems I still can't get it to work once I email to someone else using my technique. I've tried your suggestion now with what I though to be my own variables: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) myArr = Array("High", "Medium", "Low") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Andrew Welsh", "Mark Watkins", "Steve Lane").Range("a:g") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub But it returns an error on the Set myRng "Compile error: wrong number of arguments or invalid property assignments." I've tried only listing one sheet and adjusting the cell range but Help just isn't explaining what's wrong or what I should be doing clear enough for me to figure it out. "Dave Peterson" wrote: You haven't shared many details, so I used: With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With You'll want to change the name of the sheet with the list from Hidden to what you use. I used A1 through the last used cell in column A. You'll have to modify that to use the range you want. And the sort statement should be what you need, too. If this doesn't help, then post back with your modified code and more details. ATChurch wrote: No, I didn't, as when I looked for it I couldn't find it (/blush). "Notify me of replies" doesn't seem to be working for me either. Since I put more detail into this one, I'll continue here (from your earlier post) Thanks for the code, although I have no programming training at all, I can be Ok at picking out what parts do what if I know what I'm looking at, but this has me at a total loss. I just don't know where to begin editing it for my use. "Dave Peterson" wrote: Option Explicit Sub testme01() Dim myArr As Variant Dim myListNumber As Long Dim myRng As Range With Worksheets("Hidden") myArr = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With 'or lose the hidden sheet and do it all in code??? 'myArr = Array("Grape", "Apple", "Orange", "Banana", "Melon") Application.AddCustomList listarray:=myArr myListNumber = Application.GetCustomListNum(myArr) Set myRng = Worksheets("Sheet999").Range("a1:k30") With myRng .Cells.Sort key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=myListNumber + 1 End With Application.DeleteCustomList myListNumber End Sub "Dave Peterson" wrote: Did you read the replies to yesterday's post? ATChurch wrote: Hi there, I thought I posted a thread about this, but can't seem to find it. Anyway, the problem is I have the macro below. It works when I run it manually from the macro menu, but bugs when I use VB to edit it into running automaticaly (tried Wookbook_Open and Wookbook_BeforeSave). Using Ex2003 by the way. It's supposed to sort all sheets according to High, Medium, Low by: 1-Convert High, Medium, Low to 1,2,3 accross workbook. 2-Sort each sheet according to 1,2,3 3-Convert 1,2,3 back into High, Medium, Low. Now when I run it automatically, if there is High, Medium, Low but not yet sorted, then it will convert it into 1,2,3 then stop. If High, Medium, Low is already present and sorted, then it will skip converting it and just resort it as High, Low, Medium. I've previously tried a custom list to do this, but those seem to be stored locally, not in the actual wookbook, which means its not an ideal as I need to pass this around collegues who may not always use the same PC. Additionally, they're not the most computer literate, so I don't want anything more complicated than "Openenter datasaveclose" for them. The macro is: Cells.Replace What:="High", Replacement:="1", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Medium", Replacement:="2", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Low", Replacement:="3", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Andrew Welsh").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Mark Watkins").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Sheets("Steve Lane").Select Columns("A:G").Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range( _ "E2"), Order2:=xlDescending, Key3:=Range("B2"), Order3:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal Cells.Replace What:="3", Replacement:="Low", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="2", Replacement:="Medium", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="1", Replacement:="High", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro is not working properly | Excel Discussion (Misc queries) | |||
my sum function is not working properly | Excel Discussion (Misc queries) | |||
But not working properly | Excel Discussion (Misc queries) | |||
Macro not working properly when the file is shared | Excel Worksheet Functions | |||
Macro "1 tall/1 wide" not working properly | Excel Worksheet Functions |