I'm trying to make a simple macro in Excel to match printer models in one Worksheet with appropriate accessories in another worksheet and put them next to each other in a third worksheet by finding matching key words using the MID and SEARCH functions

Sub matching()
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer

    n = 8

    For i = 2 To 36
        For j = 2 To 37   
            If Mid(Worksheets.(2).Cells(i,1).Value, Application.WorksheetFunction.Search("OKI",Worksheets.(2).Cells(i,1).Value)+4,5) = Mid(Worksheets.(3).Cells(i,1).Value, Application.WorksheetFunction.Search("OKI",Worksheets.(3).Cells(i,1).Value)+4,5) Then
                 Worksheets(1).Cells(n, 1).Value = Worksheets(2).Cells(i, 1).Value
                 Worksheets(1).Cells(n, 2).Value = Worksheets(3).Cells(j, 1).Value
                 n = n + 1
            End If
        Next j
    Next i
End Sub

The macro is supposed to find the matching products and place them together. Instead, I get an error message

"Compile Error: Syntax Error"

when I try to run it and a "Compile Error: Expected: identifier or bracketed expression" From automatic syntax checker, which also highlights the marked opening bracket

  • 3
    you have Worksheets.(2) there should not be . there. – Scott Craner Aug 13 '19 at 14:33
  • Good catch - also If your Search doesn't find a match it will return an error at runtime so if there's any cells that don't contain OKI in the range it will fail – Harassed Dad Aug 13 '19 at 14:42
  • Thank you very much, Scott! That got rid of the Compile errors! Harassed Dad - you are also correct here. Even though every checked cell has a single occurrence of "OKI", I still get a Runtime Error 1004, saying that it cannot get the Search from WorksheetFunction. I've based my solution on a simpler one where I used MID and Search in the Worksheet and then only used VBA to match the already extracted results, but it seems to not work as well using VBA for all of it. – Kamel Aug 13 '19 at 15:01

Your Answer

By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.