Change code IF to CASE

[marreco]

Hi.
I would like to change this structure IF to CASE.
can anyone help me?
Thank you!

Sub TestCase()
    Dim LR As Long
    Rows(6).Resize(100).Delete
    If Range("a2") = "" And Range("b2") <> "" And Range("c2") <> "" Then
    
    With Sheets("test").UsedRange
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With
    ElseIf Range("a2") <> "" And Range("b2") = "" And Range("c2") <> "" Then
    
    With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With
    ElseIf Range("a2") <> "" And Range("b2") <> "" And Range("c2") = "" Then
     With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
    ElseIf Range("a2") = "" And Range("b2") = "" And Range("c2") <> "" Then
     With Sheets("test").UsedRange
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
    ElseIf Range("a2") <> "" And Range("b2") = "" And Range("c2") = "" Then
     With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
    ElseIf Range("a2") = "" And Range("b2") <> "" And Range("c2") = "" Then
     With Sheets("test").UsedRange
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
    Else
   With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With

    End If
End Sub

Thank's


Cross-Post
http://www.excelkey.com/forum/viewto...7&p=3566#p3566

[Franck Knight]

I'm not sure, but Case won't work with this.

Case only works on a single data. Your example has 3 fields that needs to be checked, and Case doesn't use Booleans (if not mistaken).

But you can work around it to some degree, you'll still need a IF though.

Something like this

Sub TestCase()
    Dim LR As Long
    Rows(6).Resize(100).Delete
    If Range("a2") = "" And Range("b2") <> "" And Range("c2") <> "" Then DataType = 1
    If Range("a2") <> "" And Range("b2") = "" And Range("c2") <> "" Then DataType = 2
    If Range("a2") <> "" And Range("b2") <> "" And Range("c2") = "" Then DataType = 3
    If Range("a2") = "" And Range("b2") = "" And Range("c2") <> "" Then DataType = 4
    If Range("a2") <> "" And Range("b2") = "" And Range("c2") = "" Then DataType = 5
    If Range("a2") = "" And Range("b2") <> "" And Range("c2") = "" Then DataType = 6
    If Range("a2") <> "" And Range("b2") <> "" And Range("c2") <> "" Then DataType = 7
    
Select Case DataType
Case 1


    With Sheets("test").UsedRange
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With
   
Case 2
    With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With

Case 3
     With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
Case 4
     With Sheets("test").UsedRange
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
Case 5
    ElseIf Range("a2") <> "" And Range("b2") = "" And Range("c2") = "" Then
     With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
Case 6

     With Sheets("test").UsedRange
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .Copy Range("a6")
        .AutoFilter
    End With
    
Case Else

   With Sheets("test").UsedRange
        .AutoFilter Field:=1, Criteria1:=Range("a2")
        .AutoFilter Field:=3, Criteria1:=Range("b2")
        .AutoFilter Field:=11, Criteria1:=Range("c2")
        .Copy Range("a6")
        .AutoFilter
    End With

End Select
End Sub

Just my suggestion.

[marreco]

The code is generating error 1004

[marreco]

Work fine!!

[rylo]

Hi

Same sort of workaround, but without using IF statements

dim tester as string
tester = CStr(Len(Range("a2")) = 0) & CStr(Len(Range("b2")) = 0) & CStr(Len(Range("c2")) = 0)

select cast tester
  case "TrueTrueTrue"

  case "FalseTrueTrue"

....

end select

rylo

[marreco]

Hi. Rylo

I found it interesting but I think I still would not know the code you got.

Thank you!!