Comparing a variable number of arguments using "or"

i8lunch

New Member
Joined
Sep 25, 2006
Messages
4
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!
 

Some videos you may like

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
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Sep 25, 2006
Messages
4
Thankyou for your response, I think this was the smart approach that I was racking my brain to find. Now to apply it!
 

Watch MrExcel Video

Forum statistics

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