• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Passing variables to sort a sheet by two columns

Excel 2003
vba routine


I have some code that sorts a sheet based another sheets value.


Sheets("Sheet2").Activate
   c = Sheets("Sheet2").Range("K1").Value
   w = Sheets("Sheet2").Range("L1").Value

If c = "SKU" Then
   
   Sheets("List").Activate
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
     
End If

If c = "MFRNUM" Then
   
   Sheets("List").Activate
    Cells.Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
     
End If


I plan ion adding another cell to the sort..
w = Sheets("Sheet2").Range("L1").Value

so I need the sheet to be sorted by variables  c and w.
So I need the w added to the "If.... Then" statement


Thanks
fordraiders


0
Fordraiders
Asked:
Fordraiders
1 Solution
 
Rory ArchibaldCommented:
Hard to be specific based on that, but something like:
   Dim rngKey1           As Range
   Dim rngKey2           As Range
   c = Sheets("Sheet2").Range("K1").Value
   w = Sheets("Sheet2").Range("L1").Value

   With Sheets("List")
      If c = "SKU" Then
         Set rngKey1 = .Range("A1")
      ElseIf c = "MFRNUM" Then
         Set rngKey1 = .Range("B1")
      End If
      If w = "value1" Then
         Set rngKey2 = .Range("C1")
      ElseIf w = "value2" Then
         Set rngKey2 = .Range("D1")
      End If

      .Cells.Sort Key1:=rngKey1, Order1:=xlAscending, key2:=rngKey2, order2:=xlAscending, Header:=xlGuess, _
                  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                  DataOption1:=xlSortNormal

   End With

Open in new window


If the values in those cells might not be one or the other, then you'll need to handle that appropriately.
0
 
ukerandiCommented:
use drop down box
0
 
FordraidersAuthor Commented:
Thanks !
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now