Pfthb

I need to find out how to write some basic code that will take each cell's value (which will be an ID number) from a selected range, then match it to a cell in a master workbook, copy said cell's entire row, then insert it into the original document in place of the ID number. Here's the kicker: certain ID numbers may match with several items, and all items that have that number must be inserted back into the document. Here's an example:

Master Document Workbook
A B C D A B C D
1 a ab ac 2
2 b bc bd 3
2 b be bf 
3 c cd de

I would select the cells containing 2 and 3 in the Workbook, which after running the code would give me this:

Workbook
A B C D
2 b bc bd
2 b be bf 
3 c cd de

Here's what I have going on so far but it's a total mess. The only thing it's managed to successfully do is store the selected range in the Workbook I want to paste to. It won't compile past that because I don't understand much of the syntax in VBA:

Sub NewTest()
Dim rng As Range
Dim FirstRow As Range
Dim CurrentCol As String
Dim FirstRowVal As Integer
Dim CurrentColVal As Variant
Dim rngOffset As Range

CurrentCol = "Blah"
Set FirstRow = Application.InputBox("Select the row containing your first raw material", Type:=8)
FirstRowVal = FirstRow.Row

Set rng = (Application.InputBox("Select the cells containing your IC numbers", "Obtain Materials", Type:=8))
Set rngOffset = rng.Offset(0, FirstRowVal)
CurrentColVal = rng.Column

Call CopyPaste

End Sub

Sub CopyPaste()
Dim Blah As Range
Set x = Workbooks.Open("Workbook Path")
Workbooks.Open("Workbook Path").Activate


Set y = Workbooks.Open("Master Path")
Workbooks.Open("Master Path").Activate

With x
For Each Cell In rng
x.Find(rng.Cell.Value).Select
If Selection.Offset(0, -1) = Selection Then
Selection.EntireRow.Copy
Selection = Selection.Offset(0, -1)
Else
Selection.EntireRow.Copy
Blah = Selection
End If
Workbooks.Open("Workbook Path").Activate
Sheets("Formula Sheet").Select
Blah.Insert (rng.Cell)
End

Sheets("sheetname").Cells.Select
Range("A1").PasteSpecial
'Sheets("sheetname").PasteSpecial
.Close
End With

With x
.Close
End With
End Sub

Would very much appreciate anyone who could help point me in the right direction. Thanks.

I'll bite, you can use the output array to populate any range on any worksheet.

Sub FindAndMatch()

 Dim arrMatchFrom() As Variant, arrMatchTo() As Variant, arrOutput() As Variant
 Dim i As Integer, j As Integer, counter As Integer

 counter = 0

 arrMatchFrom = Range("A2:D6")
 arrMatchTo = Range("G2:G3")

 For i = LBound(arrMatchTo, 1) To UBound(arrMatchTo, 1)
 For j = LBound(arrMatchFrom, 1) To UBound(arrMatchFrom, 1)
 If arrMatchTo(i, 1) = arrMatchFrom(j, 1) Then
 counter = counter + 1
 ReDim Preserve arrOutput(4, counter)
 arrOutput(1, counter) = arrMatchTo(i, 1)
 arrOutput(2, counter) = arrMatchFrom(j, 2)
 arrOutput(3, counter) = arrMatchFrom(j, 3)
 arrOutput(4, counter) = arrMatchFrom(j, 4)

 End If
 Next
 Next

 For i = 1 To counter
 For j = 1 To 4
 Debug.Print arrOutput(j, i)
 Cells(9 + i, j) = arrOutput(j, i)
 Next
 Next

End Sub