Sub MyMatchingMacro()
Dim nameRng As Range
Dim cell As Range
Dim finRes As Long
Dim str As String
Dim lc As Long
' Set the range of names to look through
Set nameRng = Range("A2:A6")
' Get the value to compare to
finRes = Range("B10")
' Enter prefix in string
str = "The final result has been correctly guessed by "
' Loop through all cells
For Each cell In nameRng
' Check to see if guess matches final result
If cell.Offset(0, 1).Value = finRes Then
' Add name to string
str = str & cell & ", "
End If
Next cell
' Check to see if last two characters are ", "
If Right(str, 2) = ", " Then
str = Left(str, Len(str) - 2)
' Check to see if any other commas are in string
If InStr(str, ",") > 0 Then
lc = InStrRev(str, ",")
str = Left(str, lc - 1) & " &" & Mid(str, lc + 1)
End If
Else
str = "No one guessed correctly"
End If
' Return message
MsgBox str
End Sub