Case inside For loop?

[always_confused]

Hello. I have 3 different types of data in Range("A:A") : warnings, errors, good data. The criteria that makes a value either warning, error or good is stored in cells C1, C2, C3, C4. If the value of a cell in A:A is between C1 and C2 or between C2 and C3 it is a warning. If it is greater than C3 or less than C1 it is an error. And if it is between C2 and C3 it is good. I would like to count the number of good, error and warning data.

I have set up a for loop with a case inside that increments counting variables depending on the value, but I cannot get it to work (Error Next without For). Is it possible to do this kind of counting in this way? I tried CountIf, bu it got really unwieldy pretty quickly.

What I have so far is:

Dim count_warning As Integer
Dim count_error As Integer
Dim count_good As Integer
Dim lastrow As Long
count_count_good = 0
count_alarm = 0
count_defaut = 0
lastrow = Sheets("MySheet").Range("A" & Rows.count).End(xlUp).Row

For i = 2 To lastrow
    Select Case Range("A" & i).Value

        Case (Range("K" & i).Value < Range("C2").Value And Range("K" & i).Value > Range("C1").Value) 
            count_warning =count_warning + 1
        Case (Range("A" & i).Value < Range("C4").Value And Range("K" & i).Value > Range("C3").Value) 
            count_warning = count_warning + 1

        Case (Range("A" & i).Value < Range("C3").Value And Range("K" & i).Value > Range("C2").Value) 
            count_good = count_good + 1

        Case (Range("A" & i).Value < Range("C1").Value) 
            count_error = count_error + 1
        Case (Range("A" & i).Value > Range("C4").Value)
            count_errort = count_error + 1
Next i

 

[mart37]

end select forgotten

 

[always_confused]

end select forgotten

Oh my god thank you

 

[always_confused]

end select forgotten

However, it still doesn't work. Returns 0 warnings, 0 errors and 0 good data.

 

[mart37]

You use select case wrong. You need IF THEN ELSIF THEN END IF
example:
If (Range("K" & i).Value < Range("C2").Value And Range("K" & i).Value > Range("C1").Value) Then count_warning = count_warning + 1
ElseIf (Range("A" & i).Value < Range("C4").Value And Range("K" & i).Value > Range("C3").Value) Then count_warning = count_warning + 1
etc.
End If

 

[always_confused]

You use select case wrong. You need IF THEN ELSIF THEN END IF
example:
If (Range("K" & i).Value < Range("C2").Value And Range("K" & i).Value > Range("C1").Value) Then count_warning = count_warning + 1
ElseIf (Range("A" & i).Value < Range("C4").Value And Range("K" & i).Value > Range("C3").Value) Then count_warning = count_warning + 1
etc.
End If

I replaced my code with that you suggested, but I still get 0 everywhere

 

[mart37]

Can you place your code here.
Can you place your sheet here with: XL2BB version 2 (18th Jan 2021)

 

[always_confused]

Can you place your code here.
Can you place your sheet here with: XL2BB version 2 (18th Jan 2021)

Totally started over from scratch using your method and it works now, must have had a spelling mistake or something somewhere. Thank you for you help