Clever Geek Handbook

Excel VBA condition negative IF

We have a company macro that clears the data for us - formatting and ordering the data, but over time our datasets have shifted. Thus, the macro now gives "false positives", as it were, that I want to exclude, but I do not know how to do it. Looking through the forms, I thought that if Else might work, but I can't get it to work, and I'm wondering if this is part of it, because everything is so similar.

The macro has about 15 lines similar to those below

If InStr(1, Cells(i, placementcol), "RES") Then Cells(i, sitecol) = "Resolution"
If InStr(1, Cells(i, placementcol), "FBK") Then Cells(i, sitecol) = "Facebook"
If InStr(1, Cells(i, placementcol), "ATH") Then Cells(i, sitecol) = "Authority"

The problem is in the latter, where words, including "ATH", are fetched and replaced with "Power" in a clean dataset when they shouldn't be. How can I tell the macro to ignore anything in placecol that starts with "EATH" or other combinations that I want to exclude?

+3


source to share


2 answers


You can add additional conditions to the operator If

. You can use something from the form

If

the condition you want

And

Not

condition you don't want

And

Not

some other condition that you don't want ...

Then

whatever you want to do.

For example:

If InStr(1, Cells(i, placementcol), "ATH") _
  And InStr(1, Cells(i, placementcol), "EATH")<>1 Then 
    Cells(i, sitecol) = "Authority"
End If

(c _

to split the statement over multiple lines).

In this case it is the InStr(...)<>1

same as Not (InStr(...)=1)

, but shorter to write :).

Edit . Expanding on @Jeeped's question, InStr(1,...)

starts searching on the first character, but can be anywhere. Eg InStr(1,"foo","o")=2

.

  • If you're looking anywhere in the string, you can delete 1,

    .

  • If you are only looking for cell values ​​that start with the given text, replace

    If InStr(1, Cells(), "")

    from

    If InStr(Cells(), "")=1


By the way, to avoid problems along the way, see this answer regarding usage With

... .Cells()

and not unqualified Cells

.

Edit 2

Based on your comment, I think you are looking anywhere on the line, not just at the beginning of the line. Then try something like this, for one condition:

Dim celltext As String
celltext = CStr(Cells(i, placementcol).Value)
If ( InStr(celltext, "ATH") >= 1 ) _
  And ( InStr(celltext, "EATH") = 0 ) Then 
    Cells(i, sitecol) = "Authority"
End If

(For other operators, If

you can reuse celltext

- you don't have to repeat lines Dim

and celltext=...

every time.)

It does four things differently:

  • Uses parentheses around each test case to ensure there are no misunderstandings;

  • Fairly checks ATH

    for >= 1

    , that is, somewhere in the line;

  • Fairly checks EATH

    for = 0

    , i.e. anywhere in the line; and

  • Uses celltext

    to make sure that you really have real text from Cells(i, placementcol)

    , and not Null

    or some other meaning.

I also took out the lead 1,

s.

+1


source


This is how " " VBA: Like 

If Cells(i, placementcol) Like "*[A-DF-Z]ATH*" Then Cells(i, sitecol) = "Authority"

*

matches any 0 or more characters, but [A-DF-Z]

matches any letter other than E, so it will not match lines containing EATH (unless they contain more than one ATH)



In addition, it ElseIf

can be used to ignore other checks:

 If Cells(i, placementcol) Like "*FBK*" Then
     Cells(i, sitecol) = "Facebook"
 ElseIf Cells(i, placementcol) Like "*EATH*" Then
     ' ignore
 ElseIf Cells(i, placementcol) Like "*ATH*" And Then
     Cells(i, sitecol) = "Authority"
 End If
+1


source






More articles:


All Articles