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 FunctionExample: