Comparing a variable number of arguments using "or"

[i8lunch]

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!

 

[DonkeyOte]

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.

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]

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