CODEWITHSUNDEEP
vbaexcel
Joseph Erickson
Joseph Erickson

Reputation: 960

VBA If cell equals “” Name " then sort

I'm new to . I need someone with more experience to look at this for like 4 seconds to educate me. :)

My problem is the first part is good (as seen below). Based on the names in the cell D5, (like Don, or Keith) I want the sorting to occur. For this first part it works.

this part works

Sheets("Input Tab").Select 
Range("D5").Select
If Selection.Value = "Don" Then

Sheets("Cost Center Comparison").Range("$A$5:$P$815").AutoFilter Field:=2, Criteria1:=Array("10" _
    , "11", "12", "13", "14", "15", "20", "21", "30", "51", "52", "54", "55", "57", "58", "60"), Operator:=xlFilterValues

issues with ElseIf here

Then I want to go back to that same cell (D5) to continually evaluate it. If the name changes, so will the filter. 

 ElseIf Sheets("Input tab").Range("D5").Selection.Value = "Job/Bob" Then
 Sheets("Cost Center Comparison").Range("$A$5:$P$815").AutoFilter Field:=2, Criteria1:=Array("12"), Operator:=xlFilterValues

The ElseIf for some reason doesn't work, and I don't understand the syntax well enough to know why. Any help?

Upvotes: 0

Views: 1155

Answers (1)

Tim Williams
Tim Williams

Reputation: 166735

Something like this might work better for you... 

Sub FilterOnName()    
    Dim nm As String, arr As Variant    
    nm = ActiveWorkbook.Sheets("Input Tab").Range("D5").Value

    Select Case nm
        Case "Don": arr = Array("10", "11", "12", "13", "14", "15", _
                                "20", "21", "30", "51", "52", "54", _
                                "55", "57", "58", "60")

        Case "Job/Ron": arr = Array("12")        
        'Case Else: define a default here if no match
    End Select

    If Not IsEmpty(arr) Then        
        ActiveWorkbook.Sheets("Cost Center Comparison").Range("$A$5:$P$815") _
               .AutoFilter Field:=2, Criteria1:=arr, Operator:=xlFilterValues        
    End If        
End Sub

Upvotes: 1

Related Questions