Case inside For loop?

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
68
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
Solution
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
 
Upvote 0
Can you place your code here.
Can you place your sheet here with: XL2BB version 2 (18th Jan 2021)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top