I have two excels Book1.xlsm and Book2.xlsx. Book1 will have certain values like alpha, beta, gamma etc. (no repetition) in column A. And Book2 will have multiple occurrence of Book1 values like beta, beta, beta, alpha, alpha, gamma, gamma, gamma, gamma, gamma etc. The values in Book2 may not be alphabetically sorted but same values will be grouped together. Book2 values will be also in column A.
I have a macro designed in Book1.xlsm that should iterate over each value in Book1 column A and find the first row id where same value is present in Book2 column A. This row id should be then copied in corresponding column B of Book1. This is how my macro code looks like. When I run, it fails with Run Time error '1004': Application-defined or object-defined error
Option Explicit
Sub Get_Data()
Dim wb1 As Worksheet
Dim wb2 As Worksheet
Dim wb2row As Integer
Dim i As Integer
Dim j As Integer
Const A = "A"
Const B = "B"
Set wb1 = Workbooks("Book1.xlsm").Worksheets("Sheet1")
Set wb2 = Workbooks("Book2.xlsx").Worksheets("Sheet1")
'Both For loop start from row id 2.
For i = 2 To wb1.Range("A2", wb1.Range("A2").End(xlDown)).Rows.Count
For j = 2 To wb2.Range("A2", wb2.Range("A2").End(xlDown)).Rows.Count
wb2row = Application.WorksheetFunction.Match(wb1.Cells(i, A), Range(wb2.Cells(j, A)), 0)
wb1.Cells(i, B).Copy (wb2.Cells(j, A))
Exit For ' j loop
Next j
Next i
End Sub
You can make excel do the work for you. Try this (tested)
Sub Get_Data()
With Workbooks("Book1.xlsm").Sheets("Sheet1")
With .Range(.Range("B2"), .Range("A" & Rows.Count).End(xlUp).Offset(0, 1))
.Formula2 = "=IFERROR(MATCH(A2,[Book2.xlsx]Sheet1!$A:$A,0),"""")"
.Value2 = .Value2
End With
End With
End Sub
Thank You this worked perfectly fine