Excel: Summarise
Sometimes you need to summarise a bunch of cells based on some kind of priority. For instance, you might need to know which is the highest verification method across your verification program. Or you might need to know the highest risk factor. Doing this in Excel would require a very complicated formula with nested IFs. A VBA formula is a lot easier and far less error prone (just imagine checking every formula in every cell if there's 6 nested if statements!)
Public Function VMSummarise(ParamArray Text1() As Variant) As String
Dim RangeArea As Variant
Dim Cell As Range
VMSummarise = ""
Dim CONCAT As String
For Each RangeArea In Text1
If TypeName(RangeArea) = "Range" Then
For Each Cell In RangeArea
If Len(Cell.Value) <> 0 Then
CONCAT = CONCAT & Cell.Value
End If
Next Cell
Else
'Text String was Entered
CONCAT = CONCAT & RangeArea
End If
Next RangeArea
If InStr(CONCAT, "Analysis") Then VMSummarise = "Analysis"
If InStr(CONCAT, "Inspection") Then VMSummarise = "Inspection"
If InStr(CONCAT, "Demonstration") Then VMSummarise = "Demonstration"
If InStr(CONCAT, "Test") Then VMSummarise = "Test"
If InStr(CONCAT, "Audit") Then VMSummarise = "Audit"
End Function
Example: