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!
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!