Syntax error when searching for a specific value

[ipbr21054]

Im trying with the code below to look in column C for the value VA2 BLADE & return the row number,depending on if i select Yes or No depends if code stops or clooks for next instance.
My error is the code in Red

Private Sub VA2_Click()
    Dim myRange As Range
    Set myRange = Range("Table23").ListObject.DataBodyRange
    For Each myCell In Intersect(Columns("C"), myRange) 
    If Value = ("VA2 BLADE") (myCell) Then
      With Range("A" & myCell.Row)
        If MsgBox("VA2 FOUND AT ROW: " & myCell.Address(0, 0) & vbCr & vbCr & _
          "THE CUSTOMER IS : " & .Value & vbCr & vbCr & _
          "IS THIS THE CUSTOMER YOU ARE LOOKING FOR ?", vbCritical + vbYesNo, "CUSTOMER INVOICE SEARCH") = vbYes Then
          .Select
          Exit Sub
        End If
      End With
    End If
  Next myCell
  MsgBox "THERE ARE NO MORE" & vbNewLine & vbNewLine & "SO THE SEARCH IS NOW COMPLETE", vbInformation, "VA2 SEARCH MESSAGEE"
  Range("A5").Select
End Sub

 

[6StringJazzer]

I have no idea where you got that syntax. Here is a correction.

    If myCell.Value = "VA2 BLADE" Then

However, if you are just looking for the first match (looks like you are because you Exit Sub after you find it), you can find it directly without looping.

Private Sub VA2_Click()

   Dim myRange As Range
   Dim myCell As Range
  
   Set myRange = Range("Table23").ListObject.DataBodyRange
  
   Set myCell = Intersect(Columns("C"), myRange).Find(what:="VA2 BLADE", lookat:=xlWhole)
  
   If myCell Is Nothing Then
      MsgBox "THERE ARE NO MORE" & vbNewLine & vbNewLine & "SO THE SEARCH IS NOW COMPLETE", vbInformation, "VA2 SEARCH MESSAGEE"
      Range("A5").Select
     
   Else
  
      With Range("A" & myCell.Row)
         If MsgBox("VA2 FOUND AT ROW: " & myCell.Address(0, 0) & vbCr & vbCr & _
         "THE CUSTOMER IS : " & .Value & vbCr & vbCr & _
         "IS THIS THE CUSTOMER YOU ARE LOOKING FOR ?", vbCritical + vbYesNo, "CUSTOMER INVOICE SEARCH") = vbYes Then
            .Select
         End If
      End With
   End If
  
End Sub

 

[ipbr21054]

Hi,
Ive now aded that.

I run the code & i am told that a match in found in cell C30 is that the customer i am looking for.
If i select OK on the Msgbox then that customer is selected.
If i select No it is supposed to then go to the next match BUT on clicking No the code just exits.

Private Sub VA2_Click()
   Dim myRange As Range
   Dim myCell As Range
  
   Set myRange = Range("Table23").ListObject.DataBodyRange
  
   Set myCell = Intersect(Columns("C"), myRange).Find(what:="VA2 BLADE", lookat:=xlWhole)
  
   If myCell Is Nothing Then
      MsgBox "THERE ARE NO MORE" & vbNewLine & vbNewLine & "SO THE SEARCH IS NOW COMPLETE", vbInformation, "VA2 SEARCH MESSAGEE"
      Range("A5").Select
     
   Else
  
      With Range("A" & myCell.Row)
         If MsgBox("VA2 FOUND AT ROW: " & myCell.Address(0, 0) & vbCr & vbCr & _
         "THE CUSTOMER IS : " & .Value & vbCr & vbCr & _
         "IS THIS THE CUSTOMER YOU ARE LOOKING FOR ?", vbCritical + vbYesNo, "CUSTOMER INVOICE SEARCH") = vbYes Then
            .Select
         End If
      End With
   End If
End Sub

 

[ipbr21054]

My error,the below fixed it

Private Sub VA2_Click()
    Dim myRange As Range
    Set myRange = Range("Table23").ListObject.DataBodyRange
    For Each myCell In Intersect(Columns("C"), myRange)
    If myCell.Value = "VA2 BLADE" Then
      With Range("A" & myCell.Row)
        If MsgBox("VA2 BLADE LOCATED AT ROW: " & myCell.Address(0, 0) & vbCr & vbCr & _
          "THE CUSTOMER IS : " & .Value & vbCr & vbCr & _
          "IS THIS WHAT YOU ARE LOOKING FOR ?", vbCritical + vbYesNo, "VA2 BLADE SEARCH") = vbYes Then
          .Select
          Exit Sub
        End If
      End With
    End If
  Next myCell
  MsgBox "THERE ARE NO TO BE FOUND" & vbNewLine & vbNewLine & "SO THE SEARCH IS NOW COMPLETE", vbInformation, "VA2 MESSAGEE"
  Range("A5").Select
End Sub