Macro not working properly when run automatically

[ATChurch]

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 "Open>enter data>save>close" 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]

Did you read the replies to yesterday's post?

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 "Open>enter data>save>close" 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

 

[ATChurch]

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]

You haven't shared many details, so I used:

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]

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.

 

[ATChurch]

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.

 

[ATChurch]

Oh! The column which contains High/Medium/Low is G, incase that matters.

 

[Dave Peterson]

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]

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]

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.