Case inside For loop?

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
59
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
59
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
Can you place your code here.
Can you place your sheet here with: XL2BB version 2 (18th Jan 2021)
 

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
59
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,075
Messages
5,628,498
Members
416,322
Latest member
Corbett

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top