# Comparing a variable number of arguments using "or"

#### i8lunch

##### New Member
Hi there,

I have been trying to work out how to use "Or" when I have a variable number of expressions to compare it to. As I don't really know that "or" is the correct approach to this problem I am having trouble describing it with the right terminology. Hence, I copy in my code then describe the probelm below.

'Determine if the result is greater than the criteria
Dim CriteriaBackground, CriteriaEQL, CriteriaEnv, CriteriaEIL, CriteriaHIL

For jcount = 9 + NumberOfCriteria To LogLastRow
For kcount = 6 To 5 + NumberOfAnalytes
If Cells(7, kcount) <> "" Then
CriteriaBackground = Cells(7, kcount)
Else: CriteriaBackground = 1000
End If
If Cells(8, kcount) <> "" Then
CriteriaEnv = Cells(8, kcount)
Else: CriteriaEnv = 1000
End If
If Cells(9, kcount) <> "" Then
CriteriaEIL = Cells(9, kcount)
Else: CriteriaEIL = 1000
End If
If Cells(10, kcount) <> "" Then
CriteriaHIL = Cells(10, kcount)
Else: CriteriaHIL = 1000
End If

If Cells(jcount, kcount) > CriteriaBackground Or Cells(jcount, kcount) > CriteriaEnv Or Cells(jcount, kcount) > CriteriaEIL Or Cells(jcount, kcount) > CriteriaHIL Then
Cells(jcount, kcount + 5) = 1
Cells(jcount, kcount + 8) = Cells(4, kcount)
End If
Next kcount
Next jcount

'As you can see I have four different criteria values which are in the same table as the constant they are being compared to. This code works well. However what I want it to do is be flexible to occasions when there are more than four criteria values. At the moment it can't work for any more or less than four criteria. This is linked to the last "If" expression where the constant is compared to one criteria, then the next, etc to test if any one of them is true.

Can anyone help me with this dilema?

Thanks very much!

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### DonkeyOte

##### MrExcel MVP
Not entirely sure I follow but your logic seems to read to me as though if the value in the cell being looped is greater than any of the values in the criteria cells then it needs to update 2 cells else do nothing.

If this is the case then rather than having multiple variables for each criteria cell you need only store the MIN value of all of the criteria cells given this is the key value -- if loop cell value > MIN Value then it needs to update the 2 cells else it can ignore it.

By removing the need for different variables per criteria cell you can in turn make the number of criteria cells variable also by utilising other variables:

- critcount: store the number of criteria cells (critcount)
- critrow: store the row in which the first criteria appears (ie 7)

Using this approach you can loop each criteria cell checking the value and updating the MIN value of those criteria cells.

Hopefully this makes sense.

Code:
``````Dim critcount As Long, critcounti As Long, critrow As Long, critval As Double

critcount = 4     'number of criteria cells starting in row 7
critrow = 7      'first row containing criteria cell to be checked

For jcount = 9 + NumberOfCriteria To LogLastRow

For kcount = 6 To 5 + NumberOfAnalytes

critval = 1000

For critcounti = 1 To critcount

Select Case Cells((critrow -1) + critcounti, kcount)
Case Is <> ""
critval = WorksheetFunction.Min(critval, Cells((critrow - 1) + critcounti, kcount))
End Select

Next critcounti

If Cells(jcount, kcount) > critval Then
Cells(jcount, kcount + 5) = 1
Cells(jcount, kcount + 8) = Cells(4, kcount)
End If

Next kcount

Next jcount``````

#### i8lunch

##### New Member
Thankyou for your response, I think this was the smart approach that I was racking my brain to find. Now to apply it!

Replies
16
Views
482
Replies
5
Views
92
Replies
7
Views
152
Replies
1
Views
421
Replies
6
Views
148

1,127,165
Messages
5,623,115
Members
415,955
Latest member
ssheldon2021

### 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.

### Which adblocker are you using?

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

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