Hey All,
I'm surprised I couldn't find this exact problem in another thread. Anyway, I have 3 sheets:
RA1
RA2
Paired RA
I'm working on a macro that matches data from the first two sheets and lines it up in the third. I need it to:
-Compare file IDs (columns B) from RA1 and RA2 sheets.
-Move matching (omit non-matching) file IDs and their corresponding data (columns A, C, D) to the Paired RA sheet.
-Line-up the matching file IDs and other data on the same row.
See attached example
Paired Data Example.xlsx
The code I have so far seems to match them well enough, but it doesn't line the matches up.
Private Sub CommandButton1_Click()
Dim RA1Sh As Worksheet
Dim RA2Sh As Worksheet
Dim combSh As Worksheet
Dim RA1LastRow As Long
Dim combLastRow As Long
Dim r As Long
Dim File_ID As String
Dim RA2Row As Long
Dim found As Long
Set RA1Sh = ThisWorkbook.Sheets("RA1")
Set RA2Sh = ThisWorkbook.Sheets("RA2")
Set combSh = ThisWorkbook.Sheets("Combined RA")
combLastRow = combSh.Cells(Rows.Count, "b").End(xlUp).Row
RA1LastRow = RA2Sh.Cells(Rows.Count, "b").End(xlUp).Row
For r = 2 To RA1LastRow
Set found = RA2Sh.Range("b:b").Find(File_ID, LookIn:=xlValues)
If Not found Is Nothing And found = File_ID Then
RA2Row = found.Row
combLastRow = combLastRow + 1
RA1Sh.Range("a" & r & ":d" & r).Copy combSh.Cells(combLastRow, "a")
RA2Sh.Range("a" & r & ":d" & r).Copy combSh.Cells(combLastRow, "e")
Else
RA1Sh.Range("a" & r).Copy combSh.Cells(combLastRow, "a")
RA2Sh.Range("a" & r & ":d" & r).Copy combSh.Cells(combLastRow, "e")
End If
Next r
MsgBox ("Pairing process successful. GO BUFFS!")
End Sub
I'm relatively new to macros. Any idea what I'm doing wrong?
THANKS!
Bookmarks