CODEWITHSUNDEEP
vbaexcel
Uniasus
Uniasus

Reputation: 107

Excel VBA negative IF condition

We have a company macro that cleans data up for us - formatting and arranging data, but as time has gone by our data sets have shifted. Thus, the macro now gives 'false positives' as it were that I want to exclude, but am not sure how. Reading around the forms, I thought that If Else might work, but I can't seem to get it to work and I wonder if part of it is because things are so similar.

Macro has a roughly 15 lines in a similar fashion 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 the last one, where words including "ATH" are being picked out and changed to 'Authority' in the clean data set when they shouldn't be. How can I tell the macro to ignore anything in placementcol that starts with "EATH" or other combinations I want to exclude?

Upvotes: 4

Views: 3642

Answers (2)

Slai
Slai

Reputation: 22876

Here is how to "Like" VBA :

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

* matches any 0 or more characters and [A-DF-Z] matches any letter except E, so it won't match strings that contain EATH (unless they contain more than one ATH)

Also, ElseIf can be used to ignore the rest of the 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

Upvotes: 1

cxw
cxw

Reputation: 17041

You can add additional conditions to your If statement. You can use something of the form

If condition you want

And Not condition you don't want

And Not some other condition 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

(with _ to break the statement across multiple lines).

In this case, InStr(...)<>1 is the same as Not (InStr(...)=1) but is shorter to write :) .

Edit Expanding on @Jeeped's question, InStr(1,...) starts searching at the first character, but may match anywhere. E.g., InStr(1,"foo","o")=2.

  • If you are searching anywhere in the string, you can remove the 1,.

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

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

    with

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

By the way, to avoid problems down the road, take a look at this answer regarding using With ... .Cells() rather than an unqualified Cells.

Edit 2

Based on your comment, I think you are looking anywhere in the string, not just at the start of the string. Then try something like this, for a single 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 the other If statements, you can reuse celltext — you don't have to repeat the Dim and celltext=... lines each time.)

This does four things differently:

  1. Uses parentheses around each test to make sure there are no misinterpretations;

  2. Expressly checks ATH with >= 1, i.e., somewhere in the string;

  3. Expressly checks EATH with = 0, i.e., nowhere in the string; and

  4. Uses celltext to make sure that you actually have the real text result from Cells(i, placementcol) and not a Null or some other value.

I also took out the leading 1,s.

Upvotes: 1

Related Questions