VBA code to find matching values

[snjpverma]

Names	Guess
John	5
Tom	5
Addy	6
Abby	5
Daddy	8
Final Result	5

There are three people's values that are matching the final Result. Msgbox required to show the names of the person whose value matches the final result.

Output: Msgbox is required as shown below:

The above Msgbox has been created manually for demo purpose.

Any help is highly appreciated.

 

[snjpverma]

The main part I am confused about is how to get the "&" before the last value in Macro.

If not a macro, then an excel formula would also do.

 

[Fluff]

What code have you already got?

 

[snjpverma]

Thanks for responding, Fluff

Sub findmatch()
Dim i As Integer, msg As String

msg = "The final result has been correctly guessed by"

For i = 2 To 6
    If Range("b" & i).Value = Range("B9") Then
        msg = msg & " ," & Range("a" & i).Value
    End If
Next i

MsgBox msg
End Sub

As shown in below output, the commas are incorrect.
There should not be a comma before the first name and last name as shown in Post # 1. The comma before last name should be replaced by "&" or "and".

 

[Snakehips]

Maybe like below?
Amend ranges to suit.

Sub Quiz()
Dim Guesses, Result As Range
Dim Winners, x As String
Dim Correct, c As Integer
Set Guesses = Range("B2:B5")
Set Result = Range("B9")

x = ","
c = 0
Correct = WorksheetFunction.CountIf(Guesses, Result)
If Correct = 0 Then
    Winners = " nobody"
    GoTo Show
End If

For Each cell In Guesses.Cells
    If cell = Result Then
    c = c + 1
    
    If c = Correct Then x = " &"
       
        Winners = Winners & x & " " & cell.Offset(0, -1)
        
    End If

Next

Show:
MsgBox "The final result has been guessed by" & Winners & "!", vbOKCancel, "RESULT!"

End Sub

Hope that helps.

 

[Joe4]

Here is what I came up with:

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

 

[snjpverma]

Thanks snakehips for responding,

I forgot to mentioned that there will always be at least one match in the result. So, we don't need to check if the match found is "0".

Secondly, when I tried your code,

the below result shows comma before the first name.

And the below result when only one match was found, the name is preceded by "&".

 

[snjpverma]

@Joe4, your code worked exactly as intended.
Thank a lot to both of you.

 

[Joe4]

You are welcome

 

[Snakehips]

A little late but I think this sorts it too

Sub Quiz()
Dim Guesses, Result As Range
Dim Winners, x As String
Dim Correct, c As Integer
Set Guesses = Range("B2:B6")
Set Result = Range("B9")

c = 0
Correct = WorksheetFunction.CountIf(Guesses, Result)

For Each cell In Guesses.Cells
    If cell = Result Then
    c = c + 1
    
    Select Case c
        Case 1
        x = ""
        Case Correct
        x = " &"
        Case Else
        x = ","
    End Select
         
        Winners = Winners & x & " " & cell.Offset(0, -1)
      Debug.Print Winners
    End If

Next
MsgBox "The final result has been guessed by" & Winners & "!", vbOKCancel, "RESULT!"

End Sub