Problem with my VB code

vmottert

Board Regular
Joined
Aug 30, 2003
Messages
215
This is for an inventory workbook. The sheet "SOA" contains many rows of inventory. The sheet "SOA_2" is an extraction of only those inventory rows on SOA that have units ordered (Units>0).

Both SOA and SOA_2 have list boxes on them that will sort the inventory 4 ways. The list boxes punch the numbers 1, 2, 3 or 4 into cell 'SOA!Y4" and then run a macro that does the sort. This all works fine except for the list box on SOA_2 (code below).

When a selection is made on the list box on SOA_2, it runs the macro below - Sub Extract_SOA2_Data(). As shown, that macro makes SOA the active sheet and goes to the macro Sub Sort_Inventory() (aslo below) to sort SOA the desired way. Then it should return to Sub Extract_SOA2_Data() and extract the ordered inventory wiht the desired sort.

When I step through it (F8) everything works fine through sorting on SOA. But I get "Run-Time error '1004' Application defined or object-defined error." and the line "Sheet2.Sort_Inventory" is highlighted in yellow.

Hope someone can spot where I have gone wrong ... (code below)


Sub Extract_SOA2_Data()

Application.ScreenUpdating = False
Sheets("SOA").Select
Sheet2.Sort_Inventory

Sheets("SOA_2").Select

Sheets("SOA").Range("Database").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("SOA_2!Y5:Y6"), CopyToRange:=Range("SOA_2!A12:W12"), _
Unique:=False

End Sub


Sub Sort_Inventory() 'Sort Inventory by Station, Daypart, CPP

Temp = Range("SOA!X8")

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set rng = Range("B12:T" & LastRow)

Range("SOA!X8").Value = 1 ' Change to CPP because CPMs don't sort right

If Range("SOA!Y4").Value = 1 Then ' Sort on Station, Daypart and CPP
rng.Sort Key1:=Range("B13"), Order1:=xlAscending, Key2:=Range("D13" _
), Order2:=xlAscending, Key3:=Range("G13"), Order3:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 2 Then ' Sort on Daypart and CPP
rng.Sort Key1:=Range("D13"), Order1:=xlAscending, Key2:=Range("C13" _
), Order2:=xlAscending, Key3:=Range("G13"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 3 Then ' Sort on GRPs H-L and CPP
rng.Sort Key1:=Range("E13"), Order1:=xlDescending, Key2:=Range( _
"G13"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 4 Then ' Sort on GRPs H-L and CPP
Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("G13"), Order1:=xlAscending, Key2:=Range("E13" _
), Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Else
End If

If Range("SOA!Y4").Value = 5 Then ' Sorts all to the bottom so avails may be added to the top
Range("A12:X312").Select
Selection.Sort Key1:=Range("U13"), Order1:=xlAscending, Key2:=Range("A13" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom
Else
End If

Range("SOA!X8") = Temp 'Resets Cost Comparison.

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

vmottert

Board Regular
Joined
Aug 30, 2003
Messages
215
Never Mind

I found it. One of my list boxes was still linked to code in another worksheet. Boy do I feel dumb!

Regards,

Vernon
 

Forum statistics

Threads
1,136,271
Messages
5,674,746
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top