VBA Target.Address to Two Cells

Question

I have to build a nested filter structure in Excel using VBA. The approach is to build two data validation lists that contain sorting indexes. I have three possible soring indexes, which is lastName, FirstName and Company. Since in the sort function doesn't allow indexes to be referred outside of the sorting range. I build those 6 possible combinations of the indexes, such as below.

Sub Macro_Sort1()
Sheets("Reports").Range("F1:CT10000").Sort Key1:=Sheets("Reports").Range("I1"), Order1:=xlAscending, Key2:=Sheets("Reports").Range("J1"), Order1:=xlAscending, Header:=xlYes
End Sub    

And now I need to assign this macro to those two data validation list, like

If Target.Address = "$B$1" and "$B$2" Then
    Select Case Target.Value2
         Case "Last" and "First"
            Call Macro_Sort1
         Case "Last" and "Company"
            Call Macro_Sort2
         Case "First" and "Last"
            Call Macro_Sort3
         Case "First" and "Company"
            Call Macro_Sort4
         Case "Company" and "Last"
            Call Macro_Sort5
         Case "Company" and "First"
            Call Macro_Sort6
   End Select
End if
End Sub

However, obviously it tells me there is an error with the Target.Address Sentence in regards to THEN.

Please help me figure out if there is other approach to achieve this.

Thanks in advance!

Update:

As I mentioned, I have two filters, and the first one can be used independently, it has macro assigned to it like below:

Sub Macro_Sort1()
Sheets("Reports").Range("F1:CT10000").Sort Key1:=Sheets("Reports").Range("I1"), Header:=xlYes, Order1:=xlAscending
End Sub

So before the code for both filters there would be code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$17" Then
    Select Case Target.Value2
         Case "Last"
            Call Macro_Sort1
         Case "First"
            Call Macro_Sort2
         Case "Company"
            Call Macro_Sort3
    End Select

Based on some advice, I appended this part after the previous one.

ElseIf Target.Address(0, 0) = "$B$17" Or Target.Address(0, 0) = "$B$18" Then
       SortUsingDataValidation Range("B17").Value, Range("B18").Value
End If
End Sub

Private Sub SortUsingDataValidation(sFirst As String, sSecond As String)
Dim oDict As Object

Set oDict = CreateObject("Scripting.Dictionary")
With ThisWorknook.shhets("Reports")
Set oDict("Last") = .Range("I1")
Set oDict("First") = .Range("J1")
Set oDict("Company") = .Range("K1")
If oDict.exists(sFirst) And oDict.exists(sSecond) Then
   .Range("F1:CT10000").Sort Key1:=oDict(sFirst), Order1:=xlAscending, key2:=oDict(sSecond), Order1:=xlAscending, Header:=xlYes
End If
End With
End Sub

Although it is really nice approach, it fails to run. There is no error or any message boxes pop up. So please check if it is code of first filter affecting the double filter code.

Thank you very much!

Answer 1

I'm going to add in another approach because it's generally not a good idea to have 6 different macros to do essentially the same thing. It will work, but it's more code to maintain, making future changes more difficult.

In the code below, the values of the validation boxes are passed into a function that (1) links each value to a corresponding range, (2) sorts based on the corresponding ranges.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B1" Or Target.Address(0, 0) = "B2" Then
        SortUsingDataValidation Range("B1").Value, Range("B2").Value
    End If
End Sub

Private Sub SortUsingDataValidation(sFirst As String, sSecond As String)
        Dim oDict As Object

        'Create a dictionary (or any collection) to hold references from a data validation
        'String to its corresponding cell reference
        Set oDict = CreateObject("Scripting.Dictionary")
        With ThisWorkbook.Sheets("Reports")
            Set oDict("First") = .Range("I1")
            Set oDict("Last") = .Range("J1")
            Set oDict("Company") = .Range("K1")
            If oDict.exists(sFirst) And oDict.exists(sSecond) Then
                .Range("F1:CT10000").Sort Key1:=oDict(sFirst), Order1:=xlAscending, Key2:=oDict(sSecond), Order1:=xlAscending, Header:=xlYes
            End If
        End With
    End Sub

Answer 2

Edited:

• added Application.Transpose() method to Target range since B1:B2 is a one-column "vertical" range while Join() expects an array which corresponds to a one-row "horizontal" range

• added some code "flavours" to deal with different environments

Changing approach

If Target.Address = "$B$1:$B$2" Then
    Dim strng As String
    Dim pos as Long

    strng = "1LastFirst,2LastCompany,3FirstLast,4FirstCompany,5CompanyLast,6CompanyLast"
    pos = Instr(strng,Join (Application.Transpose(Target),""))
    If pos > 0 then Application.Run "Macro_Sort" & Mid(strng,pos-1,1)
End If

Finally some environment advices:

In case you're running this code inside a Worksheet event handler then

• If Target.Address = "$B$1:$B$2" could be True only in a Worksheet_SelectionChange handler, while other relevant handlers dealing with a Target range parameter would detect only a one-cell Range

  For instance should you need to run it inside a Worksheet_Change event handler then you'd need to change the check into some:

  If Not Intersect(Target, Range ("B1:B2")) Is Nothing Then

  Which could also do in many other different cases

• Subs to be called by Application.Run method must be placed in any module but not in a Worksheet one

Answer 3

If Target.Address = "$B$1" And "$B$2" Then

This is a syntax error, because If {boolean-expression} Then requires, well, a Boolean expression - and you've confused VBA with the And {string-literal} part of that expression.

You could construct a Boolean expression with the And keyword, like this:

{boolean-expression} And {boolean-expression}

In other words, you could have done:

If Target.Address = "$B$1" And Target.Address = "$B$2" Then

But as you would suspect, that logic is bound to always return False, because if Target.Address is equal to "$B$1" then it can't also be equal to "$B$2".

Scott's answer has the rest of the solution.

Answer 4

A couple of things;

The address will be "B1:B2"

And to do multiple test with Select Case we want to search for True and format it something like a IF/ElseIF:

If target.Address(0, 0) = "B1:B2" Then
    Select Case True
         Case Range("B1") = "Last" And Range("B2") = "First"
            Call Macro_Sort1
         Case Range("B1") = "Last" And Range("B2") = "Company"
            Call Macro_Sort2
         Case Range("B1") = "First" And Range("B2") = "Last"
            Call Macro_Sort3
         Case Range("B1") = "First" And Range("B2") = "Company"
            Call Macro_Sort4
         Case Range("B1") = "Company" And Range("B2") = "Last"
            Call Macro_Sort5
         Case Range("B1") = "Company" And Range("B2") = "First"
            Call Macro_Sort6
   End Select
End If