Ndtyjky

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