Range object causing Sort to fail

[Steven B]

Hello,

I'm trying to program in MSExcel 2003 a sort in a workbook that I
intend to lock down to prevent users from affecting the list box
validation source and the code keeps breaking at this line:
SrcRng.Sort Header:=xlYes, DataOption1:=xlSortNormal, Key1:=SKey1,
Order1:=Dir1

I can't figure this one out. Here is the full script:
Sub KCarSort()
Dim SKey1 As Range 'Sort Key 1
Dim SKey2 As Range 'Sort Key 2
Dim SKey3 As Range 'Sort Key 3
Dim Dir1 As String 'Sort Direction 1
Dim Dir2 As String 'Sort Direction 2
Dim Dir3 As String 'Sort Direction 3

Dim SrcRng As Range
Dim SrcHdrRng As Range

With ActiveWorkbook
MsgBox (Range(.Names("skey1")).Value)
Set SrcHdrRng = Range("A1:AL1")
Set SrcRng = Range("A1:AL1001")
If Range(.Names("Skey1")).Value <> "" Then
Set SKey1 = SrcHdrRng.Find(what:=Range(.Names("Skey1")).Value)
If Range(.Names("Dir1")).Value <> "" Then
Dir1 = "xl" & Range(.Names("dir1")).Value
Else
Dir1 = "xlAscending"
End If
Else: GoTo ENDING
End If

If Range(.Names("Skey2")).Value <> "" Then
Set SKey2 = SrcHdrRng.Find(what:=Range(.Names("Skey2")).Value)
If Range(.Names("Dir2")).Value <> "" Then
Dir2 = "xl" & Range(.Names("dir2")).Value
Else
Dir2 = "xlAscending"
End If
End If

If Range(.Names("Skey3")).Value <> "" Then
Set SKey3 = SrcHdrRng.Find(what:=Range(.Names("Skey3")).Value)
If Range(.Names("Dir3")).Value <> "" Then
Dir3 = "xl" & Range(.Names("dir3")).Value
Else
Dir3 = "xlAscending"
End If
End If


If Not SKey2 Is Nothing Then
If Not SKey3 Is Nothing Then
SrcRng.Sort Key1:=SKey1.Address, Order1:=Dir1 _
, Key2:=SKey2.Address, Order2:=Dir2 _
, Key3:=SKey3.Address, Order3:=Dir3
Else
SrcRng.Sort Key1:=SKey1.Address, Order1:=Dir1 _
, Key2:=SKey2.Address, Order2:=Dir2
End If
Else
SrcRng.Sort Header:=xlYes, DataOption1:=xlSortNormal, Key1:=SKey1,
Order1:=Dir1
End If
End With
ENDING: MsgBox ("You must select at least 1 criteria in the Sort 1
dropdown to perform a sort")
End Sub


Your assistance is greatly appreciated.


Steven

 

[Don Guillett Excel MVP]

Hello,

I'm trying to program in MSExcel 2003 a sort in a workbook that I
intend to lock down to prevent users from affecting the list box
validation source and the code keeps breaking at this line:
    SrcRng.Sort Header:=xlYes, DataOption1:=xlSortNormal, Key1:=SKey1,
Order1:=Dir1

I can't figure this one out. Here is the full script:
Sub KCarSort()
Dim SKey1 As Range 'Sort Key 1
Dim SKey2 As Range 'Sort Key 2
Dim SKey3 As Range 'Sort Key 3
Dim Dir1 As String 'Sort Direction 1
Dim Dir2 As String 'Sort Direction 2
Dim Dir3 As String 'Sort Direction 3

Dim SrcRng As Range
Dim SrcHdrRng As Range

With ActiveWorkbook
MsgBox (Range(.Names("skey1")).Value)
Set SrcHdrRng = Range("A1:AL1")
Set SrcRng = Range("A1:AL1001")
        If Range(.Names("Skey1")).Value <> "" Then
        Set SKey1 = SrcHdrRng.Find(what:=Range(.Names("Skey1")).Value)
            If Range(.Names("Dir1")).Value <> "" Then
                Dir1 = "xl" & Range(.Names("dir1")).Value
            Else
                Dir1 = "xlAscending"
            End If
        Else: GoTo ENDING
        End If

        If Range(.Names("Skey2")).Value <> "" Then
        Set SKey2 = SrcHdrRng.Find(what:=Range(.Names("Skey2")).Value)
            If Range(.Names("Dir2")).Value <> "" Then
                Dir2 = "xl" & Range(.Names("dir2")).Value
            Else
                Dir2 = "xlAscending"
            End If
        End If

        If Range(.Names("Skey3")).Value <> "" Then
        Set SKey3 = SrcHdrRng.Find(what:=Range(.Names("Skey3")).Value)
            If Range(.Names("Dir3")).Value <> "" Then
                Dir3 = "xl" & Range(.Names("dir3")).Value
            Else
                Dir3 = "xlAscending"
            End If
        End If

If Not SKey2 Is Nothing Then
    If Not SKey3 Is Nothing Then
        SrcRng.Sort Key1:=SKey1.Address, Order1:=Dir1 _
            , Key2:=SKey2.Address, Order2:=Dir2 _
            , Key3:=SKey3.Address, Order3:=Dir3
    Else
        SrcRng.Sort Key1:=SKey1.Address, Order1:=Dir1 _
            , Key2:=SKey2.Address, Order2:=Dir2
    End If
Else
    SrcRng.Sort Header:=xlYes, DataOption1:=xlSortNormal, Key1:=SKey1,
Order1:=Dir1
End If
End With
ENDING: MsgBox ("You must select at least 1 criteria in the Sort 1
dropdown to perform a sort")
End Sub

Your assistance is greatly appreciated.

Steven

I don't understand what you are doing so,
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Steven B]

Hi Don,

Apologies. In retrospect the message is vague. I built a template for
a couple of users to track their data before we convert it to import
into our database. I used the dropdown validation in the worksheet for
certain cells to standardize the data entry, but the users garbled it
by performing a massive sort.

The macro is intended to sort a fixed range based on criteria the user
selects from the dropdown, same as if they had used the standard sort
dialog, but this ensures the sort range is limited.

The SKey range objects reference a Named cell which contains a
dropdown of all the Header row labels. The user picks the field they
want to sort the data by using the drop down and the sort order (e.g.
Dir1 string) - Ascending or Descending then will click a button on the
worksheet.

I have used Sort in a number of other situations, but it keeps failing
in this workbook and I can't figure out why.

This message has been copied into an email to you Don, but for the
sake of sharing information and solutions, I will continue to include
information here.

Thank you,

Steven