Macro to determine values

[ryan.bleam]

I'm using the following formula:
=IF(ISNUMBER(MATCH(1,IF($A$2:$A$500=A322,IF($L$2:$L$500="Failed",1)),
0)),"Failed",IF(ISNUMBER(MATCH( 1,IF($A$2:$A$500=A322,IF($L$2:$L
$500="Not Completed",1)),0)),"Not Completed","Passed"))

However, I want to incorporate a couple more things into the formula
and can't figure it out. I want the following to happen:
1) If anything is Failed the result should be Failed
2) If everything is Passed the result should be Passed
3) If it's No Run and Passed the result should be Not Completed
4) If everything is No Run the result should be No Run

Those are the scenarios that the formula should cover. Any help is
greatly appreciated.

Thanks in advance.

 

[JLatham]

[second attempt to post]
I'm a bit confused: your formula is testing values in rows 2 through 500
and the test value is right in the middle of it all at A322. Is this really
the case?

Because of that confusion, I'm going to assume that the test value exists
somewhere in a separate cell: M1 is where I'll put the value to test values
in A2:A# from and you can change that if you want/need to. The result will
be placed into M2 (which is the equivalent of whatever cell you had your
formula in).

Also, this code sets up to test a varying number of rows, based on the last
entry in column A.

To test this:
Make a copy of your workbook. Close the original.
Open the copy.
Press [Alt]+[F11] to open the VB Editor (VBE).
In the VBE, choose Insert --> Module from its menu toolbar.
Copy the code below, paste it into the empty module presented to you.
Edit any Const values as required to match your workbook/sheet setup.

Save the workbook and test it out: go to Tools --> Macro --> Macros and
select the
EvaluateTests macro in the list and click the [Run] button after entering a
test value to match into cell M1 (or where ever you decide to put it).

Sub EvaluateTests()
'set these Const values as needed to
'match the setup of your workbook/worksheet
'name of sheet with the data on it
Const resultsSheetName = "Sheet1"
Const testValueCell = "M1"
Const resultsCell = "M2"
Const valuesColumn = "A"
Const resultsColumn = "L"
Const firstDataRow = 2
Const passedPhrase = "Passed"
Const failedPhrase = "Failed"
Const notrunPhrase = "Not Completed"
'end of user definable values

Dim allPassedFlag As Boolean
Dim allFailedFlag As Boolean
Dim allNotRunFlag As Boolean
Dim anyFailedFlag As Boolean
Dim anyPassedFlag As Boolean
Dim anyNotRunFlag As Boolean

Dim dataSheet As Worksheet
Dim valuesRange As Range
Dim anyValue As Range
Dim testValue As Range
Dim offset2Result As Integer

Set dataSheet = ThisWorkbook.Worksheets(resultsSheetName)
If IsEmpty(dataSheet.Range(testValueCell)) Then
MsgBox "No value entered to compare.", vbOKOnly, "M1 Empty"
Set dataSheet = Nothing
Exit Sub
End If
If dataSheet.Range(valuesColumn & Rows.Count). _
End(xlUp).Row < firstDataRow Then
MsgBox "No test data entered to evaluate.", vbOKOnly, "No Test Data"
Set dataSheet = Nothing
Exit Sub
End If
'initialize flags
allPassedFlag = True
allFailedFlag = True
allNotRunFlag = True
offset2Result = Range(resultsColumn & 1).Column - _
Range(valuesColumn & 1).Column
Set valuesRange = dataSheet.Range(valuesColumn & firstDataRow _
& ":" & dataSheet.Range(valuesColumn & Rows.Count). _
End(xlUp).Address)
Set testValue = dataSheet.Range(testValueCell) ' M1
dataSheet.Range(resultsCell) = "" ' in M2: erase prior result
For Each anyValue In valuesRange
If anyValue = testValue Then
Select Case UCase(Trim(anyValue.Offset(0, offset2Result)))
Case Is = UCase(Trim(passedPhrase))
anyPassedFlag = True
allFailedFlag = False
allNotRunFlag = False
Case Is = UCase(Trim(failedPhrase))
anyFailedFlag = True
allPassedFlag = False
allNotRunFlag = False
Case Is = UCase(Trim(notrunPhrase))
anyNotRunFlag = True
allPassedFlag = False
allFailedFlag = False
Case Else
'phrase cell is empty or
'contains some other phrase
' we ignore it completely
End Select
End If
Next
'now evaluate the results
If allPassedFlag Then
dataSheet.Range(resultsCell) = passedPhrase ' in M2
ElseIf allFailedFlag Or anyFailedFlag Then
dataSheet.Range(resultsCell) = failedPhrase ' in M2
ElseIf allNotRunFlag Then
dataSheet.Range(resultsCell) = notrunPhrase ' in M2
ElseIf anyPassedFlag And anyNotRunFlag Then
dataSheet.Range(resultsCell) = notrunPhrase ' in M2
End If

Set valuesRange = Nothing
Set testValue = Nothing
Set dataSheet = Nothing
End Sub