VBA macro INSTR function in triple if statement

Question

I try to use the "instring" function to make a triple filter for a counter. The main idea is to define a range and then check every cell as follows:

1) Locate content "0111" in cell

2) If content "0111" in cell found, check on the column left of this cell, if content "127" exists

3) Check if the row of the current cell is not red (color 3)

4) If all the above is true, raise counter.

My code looks as follows:

Set SrchRng4 = Range("J1:J100")

 For Each cel In SrchRng4
   If InStr(1, cel.Value, "0111", 1) > 0 Then
   If InStr(1, ActiveCell(Offset(-1,0), "127", 1) > 0 And  cel.EntireRow.Interior.ColorIndex <> 3 Then
        count_K = count_K + 1

    End If
Next cel

It work for the search of "111" and the exeption of the red rows but it doesn't work after the addition of the "127" filter. Can anyone provide some input on that ? I suppose it some problem with the syntax ?

Kind regards, Marcus

try with If InStr(1, cel.Offset(-1,0), "127", 1) > 0 And ... — Kazimierz Jawor
– Kazimierz Jawor, Commented Oct 24, 2016 at 11:03
@KazimierzJawor5 gave you the solution except for cel.Offset(-1,0) must be cel.Offset(0,-1). Furthermore, should you have time performance issues, you may want to filter on first two conditions and then loop through filtered cells only to check the last one — user3598756
– user3598756, Commented Oct 24, 2016 at 11:09
Hallo guys, thank you for the replies. The loop does not seem to close, now the error is at the last row of the code: "compile error: Next without For" — user36510
– user36510, Commented Oct 24, 2016 at 11:18

Slai · Accepted Answer · 2016-10-24 11:42:26Z

1

For Each cel In Range("J1:J100")
    If InStr(cel, "0111") And InStr(cel( ,0), "127") _ 
        And cel.EntireRow.Interior.Color <> vbRed Then count_K = count_K + 1
Next

Numeric values that are not 0 are evaluated to True, so > 0 is optional
cel(,0) is similar to cel.Offset(0,-1) to get the cell left of cel
vbRed and rgbRed are constants for the color red, but .ColorIndex <> 3 is fine too
each If .. Then statement should have a matching End If statement, unless it is on one line

answered Oct 24, 2016 at 11:42

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA macro INSTR function in triple if statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related