Macro Assist

  • Thread starter Michael Koerner
  • Start date
M

Michael Koerner

I have a grocery store shopping list and I would like to set up a macro that would sort and display the list, if there is a value in column D which has a header name of Qty. I would like it sorted by store name/number (in reverse order ), then by Name, Aisle, and Depth. Below is the code I recorded only if there is a number greater than 1 it puts it at the end of the list since I added a new store.

The sheet headings are Item, Aisle, Depth, Store, Price, Notes. Any help as always is greatly appreciated.

Sub SortShoppingList()
'
'
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<>"
Range("A1:G1024").Sort Key1:=Range("E2"), Order1:=xlDescending, Key2:= _
Range("B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End Sub
 
N

Nigel

No sure what the issue is here? You can sort on multiple keys each in any order. Please explain more what problem you are facing.

--

Regards,
Nigel
(e-mail address removed)



I have a grocery store shopping list and I would like to set up a macro that would sort and display the list, if there is a value in column D which has a header name of Qty. I would like it sorted by store name/number (in reverse order ), then by Name, Aisle, and Depth. Below is the code I recorded only if there is a number greater than 1 it puts it at the end of the list since I added a new store.

The sheet headings are Item, Aisle, Depth, Store, Price, Notes. Any help as always is greatly appreciated.

Sub SortShoppingList()
'
'
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<>"
Range("A1:G1024").Sort Key1:=Range("E2"), Order1:=xlDescending, Key2:= _
Range("B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End Sub
 
M

Michael Koerner

So long as there is only a value of one (1) in the Qty column, the macro works just fine. If there is a different Qty amount The different items are put on the bottom of the list.

I know diddly about recording macros. What I would like it to do, if there is a value in the Qty Col C column then these are the only items I want to see after I do a sort using Stores (in reverse order) Col "E", Item description Col "A", Isle number Col "C", and Depth Col "D"

This produces a shopping list which I then print and use, then Exit Excel without saving the changes that the macro produces. Hope this helps

--

Regards
Michael Koerner


No sure what the issue is here? You can sort on multiple keys each in any order. Please explain more what problem you are facing.

--

Regards,
Nigel
(e-mail address removed)



I have a grocery store shopping list and I would like to set up a macro that would sort and display the list, if there is a value in column C which has a header name of Qty. I would like it sorted by store name/number (in reverse order ), then by Name, Aisle, and Depth. Below is the code I recorded only if there is a number greater than 1 it puts it at the end of the list since I added a new store.

The sheet headings are Item, Aisle, Depth, Qty, Store, Price, Notes. Any help as always is greatly appreciated.

Sub SortShoppingList()
'
'
'

'
Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="<>"
Range("A1:G1024").Sort Key1:=Range("E2"), Order1:=xlDescending, Key2:= _
Range("B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime error 1004 Application-defined or object defined error" on data sort 1
Macro Help 5
Sort error? 2
sort range 0
Sort by range 15
Writing a Sort Macro 4
Sort Errors 4
sorting columns 5

Top