How to break up an If-not-->then statement with multiple "nots" into a block of code?

user_15610797 0
Viewed 67

Here's the code I've written so far, it just goes and goes, I'd like to make it into a block (if possible) to make it more manageable.

The purpose of the code is to erase certain cells if none of the known names are present in a certain cell. I have other individual codes based off each name that paste specific data into specific cells if Namen is present:

Sub Eraser()
        If Range("E4").Value <> "Name1" And Range("E4").Value <> "Name2" And Range("E4").Value <> "Name3" And Range("E4").Value <> "Name4" And Range("E4").Value <> "Name5" And Range("E4").Value <> "Name6" And Range("E4").Value <> "Name7" And Range("E4").Value <> "Name8" And Range("E4").Value <> "Name9" And Range("E4").Value <> "Name10" And Range("E4").Value <> "Name11" And Range("E4").Value <> "Name12" And Range("E4").Value <> "Name13" Then
        Range("W4").Value = ""
        End If
End Sub

I've tried Things like

Sub Eraser()
        If Range("E4").Value <> "Name1" & _ 
        Range("E4").Value <> "Name2" & _
        Range("E4").Value <> "Name3" & _
        Range("E4").Value <> "Name4" & _
        Range("E4").Value <> "Name5" & _
        Range("E4").Value <> "Name6" & _
        Range("E4").Value <> "Name7" & _
        Range("E4").Value <> "Name8" & _ 
        Range("E4").Value <> "Name9" & _
        Range("E4").Value <> "Name10" & _
        Range("E4").Value <> "Name11" & _
        Range("E4").Value <> "Name12" & _
        Range("E4").Value <> "Name13" Then
        Range("W4").Value = ""
        End If
End Sub

And

Sub Eraser()
       If Range("E4").Value <> "Name1" & _
       And Range("E4").Value <> "Name2" & _
       And Range("E4").Value <> "Name3" & _
       And Range("E4").Value <> "Name4" & _
       'And et cetera'

FYI The code works perfectly when it's single line, I'm just a perfectionist

Since a number of people have offered codes that I think contradict what I was trying to do, I'll add my other codes that the Eraser Sub was meant to erase. If anyone has any ideas on how to make them more elegant or concise, I'm open.

Sub Name1()
    If Range("E4").Value = "Name1" Then
        Range("T6").Value = "EID1#"  'Employee ID Number
        Range("U10").Value = "SN1"   'Serial Number
        Range("I19").Value = "TN1"   'Trainer Name
        Range("AA19").Value = "TED1" 'Trainer Expiration Date
        Range("AB6").Value = "CN1"   'Course Name
    End If
End Sub

Sub Name2()
    If Range("E4").Value = "Name2" Then
        Range("T6").Value = "EID2"
        Range("U10").Value = "SN2"
        Range("I19").Value = "TN2"
        Range("AA19").Value = "TED2"
        Range("AB6").Value = "CN2"
    End If
End Sub

Sub Name3()
    If Range("E4").Value = "Name3" Then
        Range("T6").Value = "EID3"
        Range("U10").Value = "SN3"
        Range("I19").Value = "TN3"
        Range("AA19").Value = "TED3"
        Range("AB6").Value = "CN3"
    End If
End Sub

Sub Eraser()
    If Range("E4").Value <> "Name 1" _
        And Range("E4").Value <> "Name2" _
        And Range("E4").Value <> "Name3" Then
        Range("T6").Value = ""
        Range("U10").Value = ""
        Range("I19").Value = ""
        Range("AA19").Value = ""
        Range("AB6").Value = ""
    End If
End Sub

Again, I have about 16 different iterations for this, and while some of my team have the same information, a lot of them have different info. so I coded each one individually, and wrote an update-check code that calls each Sub automatically. The Eraser Sub was mainly to be Personally Identifiable Information (PII) conscious

For diligence sake, here's the update-check Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$4" Then
        Call Name1
        Call Name2
        Call Name3
        Call Eraser 
    End If
End Sub
if-statement vba excel
3 Answers

Firstly you missed "AND". Secondly to make a bit nicer use "Select Case"

Sub Eraser()
    Select Case (Range("E4").Value)
        Case "Name1", "Name2", "Name3", "Name4", "Name5"
            MsgBox "FOUND" 'code goes here if TRUE
        Case Else
            MsgBox "no finding" 'code goes here if FALSE
    End Select
End Sub
user_107065550

"&" should be AND. "&" is used to join strings together. AND is used to evaluate boolean expressions.

Sub Eraser()
        If Range("E4").Value <> "Name1" _
        And Range("E4").Value <> "Name2" _
        And Range("E4").Value <> "Name3" _
        And Range("E4").Value <> "Name4" _
        And Range("E4").Value <> "Name5" _
        And Range("E4").Value <> "Name6" _
        And Range("E4").Value <> "Name7" _
        And Range("E4").Value <> "Name8" _
        And Range("E4").Value <> "Name9" _
        And Range("E4").Value <> "Name10" _
        And Range("E4").Value <> "Name11" _
        And Range("E4").Value <> "Name12" _
        And Range("E4").Value <> "Name13" Then
            Range("W4").Value = ""
        End If
End Sub

Also, to save on processing time and to make the code look cleaner I suggest using variables as nicknames for commonly repeated references.

Sub Eraser()
    Dim rVal As Variant
    rVal = Range("E4").Value
    
    If rVal <> "Name1" _
        And rVal <> "Name2" _
        And rVal <> "Name3" _
        And rVal <> "Name4" _
        And rVal <> "Name5" _
        And rVal <> "Name6" _
        And rVal <> "Name7" _
        And rVal <> "Name8" _
        And rVal <> "Name9" _
        And rVal <> "Name10" _
        And rVal <> "Name11" _
        And rVal <> "Name12" _
        And rVal <> "Name13" Then
            Range("W4").Value = ""
    End If
End Sub

And Finally, if you have an increasingly large number of names to check against, I suggest adding them to a array or dictionary and creating a function to iterate through the array or using dictionary.Exists as a way to evaluate your expression.

user_146087500

Two function checks for search term existance

Just to demonstrate a couple of other simple ways to check for term existance.

Both approaches assume a comma separated list as 2nd argument (here e.g. "Name1,Name2,Name3")

a) via counting elements after execution of VBA.Filter()

A positive filtering of splitted list terms by commata results in a zero-based array. The upper boundary (UBound()) of found elements incremented by 1 results in the boolean function return value True, -1 for non-findings plus 1 results in False (-1+1=0).

Function IsListed(ByVal term As String, List) As Boolean
   Dim elems: elems = Split(LCase(List), ",")
   'return boolean function result
   IsListed = UBound(Filter(elems, LCase(term), True)) + 1
End Function

b) via Excel function FilterXML()

Transforming the passed list string into a well formed (html tag-like) xml content allows to apply Excel's FilterXML() function upon it (available since version 2013+).

Function IsListed(ByVal term As String, List) As Boolean
    Dim content: content = "<r><s>" & Replace(List, ",", "</s><s>") & "</s></r>"
   'return boolean function result
    IsListed = Not IsError(Application.FilterXML(content, "//s[.='" & term & "']"))
End Function

Possible example call

Sub Eraser()
        Dim rVal: rVal = Sheet1.Range("E4")
        If IsListed(rVal, "Name1,Name2,Name3") Then
            Debug.Print rVal & " found"                  'code goes here if TRUE
        Else
            Debug.Print rVal & " not found!"             'code goes here if FALSE
            'do further stuff
            Sheet1.Range("W4") = vbNullString     
        End If
user_64602970
Related