Excel VBA negative IF condition

Question

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?

Are they all supposed to **start** with the substring? Do you need to check for substring(s) or are you really comparing the entire cell's value? — , Apr 04 '17 at 17:53
I'm really comparing the entire cell values. "Res" or "Ath" can appear anywhere. Plus, this works and I don't want to risk breaking the macro >. — Uniasus, Apr 04 '17 at 18:41
Since you already have the one `If` you could nest a second `If` something like `If NOT InStr(1,Cells(i,placementcol), "EATH") AND NOT InStr(1,Cells(i,placementcol), "SATH") then` This isn't tested but might give you what you want with some simple modification . — Mike, Apr 04 '17 at 18:50
As a side note: If you put `@` in front of a users name (@Mike for me) in a comment it will send a notification to that user so they will know that you have responded to something that was posted. You can only do that with one user per comment, though. — Mike, Apr 04 '17 at 18:52
'*I'm really comparing the entire cell values. "Res" or "Ath" can appear anywhere.*' Maybe it is just me but these two sentences completely contradict each other. — , Apr 04 '17 at 19:05

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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:

Uses parentheses around each test to make sure there are no misinterpretations;
Expressly checks ATH with >= 1, i.e., somewhere in the string;
Expressly checks EATH with = 0, i.e., nowhere in the string; and
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.

What her block of condition statements needs is to be converted to a Select Case black or factor in ElseIf so that if the first condition is met, 14 more are not evaluated. — , Apr 04 '17 at 19:07
I wanted so badly for this to work. Sadly, it doesn't, regardless of whether or not I remove the '1' to indicate where to search or if I use `Instr(...)<>1` or `Not Instr(...)=1`. Are there other options, or could something else where in the Macro be affecting this? — Uniasus, Apr 04 '17 at 19:24
@Jeeped post an answer and I'll upvote it :) . I thought about the `ElseIf` as well but didn't think I had enough context to write a broader answer. — cxw, Apr 04 '17 at 19:57
@Jenny Try the "Edit 2" code and see what happens. Yes, it could always be something elsewhere in the macro :) . It could also be something screwy in your data. — cxw, Apr 04 '17 at 20:06
Played around with it and what worked was `If InStr(1, Cells(i, placementcol), "ATH") And InStr(1, Cells(i, placementcol), "EATH") = 0 Then Cells(i, sitecol) = "Authority"`. Thanks for all your help guys! — Uniasus, Apr 04 '17 at 21:08

score 1 · Answer 2 · answered Apr 04 '17 at 21:31

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

Excel VBA negative IF condition

2 Answers2