ExecuteExcel4Macro for closed workbook

[YasserKhalil]

Hello everyone
I have closed workbook named "Sample.xlsm" with the data in two columns A & B like that
Symbol LTP
ACC 1247
ADANIENT 152.35
ADANIPORTS 338.85
DABUR 450.55
DIVISLAB 2351.9

And in the workbook that will have the macro, I have in column A the following:
Symbol
ADANIPORTS
DIVISLAB

This is the code

Code: Select all

Sub Test()
    Dim x, myVal, r As Range, BK As String
    BK = "'" & ThisWorkbook.Path & "\[Sample.xlsm]Sheet1'!"
    With Sheets("Sheet1")
        For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            myVal = r.Value
            If Not IsNumeric(myVal) Then myVal = Chr(34) & myVal & Chr(34)
            x = ExecuteExcel4Macro("MATCH(" & r.Value & "," & BK & "R1C1:R10000C1,0)")
            If IsNumeric(x) Then
                With .Cells(r.Row, Columns.Count).End(xlToLeft)
                    .Cells(1, 2) = .Value + 1
                End With
            End If
        Next r
    End With
End Sub

When pressing F8 to follow the code I found the x returns an error
What's the wrong with this approach?

[HansV]

The line

Code: Select all

            x = ExecuteExcel4Macro("MATCH(" & r.Value & "," & BK & "R1C1:R10000C1,0)")

should be

Code: Select all

            x = ExecuteExcel4Macro("MATCH(" & myVal & "," & BK & "R1C1:R10000C1,0)")

otherwise, the quotes around text values aren't included.

[YasserKhalil]

Thank you very much for the great help