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
What's the wrong with this approach?