Finding duplicate text in a different worksheet and inserting rows

[pilatt]

My goal is to grab a 5 character text string from worksheet1, compare it to a worksheet2 and find the occurrences of that string. When a match is found, a cell from the matching row in will be copied over from worksheet2 to worksheet1. If multiple matches are found, I need to insert rows below the searching for string in worksheet1 and put data worksheet2 into them.

I am stuck on using the count function and insert rows.

The purpose is to search a demand forecast part number against a master list to grab the raw materials needed. Some part numbers contain multiple raw materials. I am trying to gather the raw material's for each part and put them together so I can get a PO ready for the raw materials needed.


I am using Excel 2010 and Windows 7.


Sorry for the long code, this is segment of my first actual program. I know it could be shorter but this is my first real program.
The area I commented is what I am having difficultly filling in.

For i = 1 To (endrow - 5)
    materialcopy = Cells(6 + i, 3).Value
    materiallen = Len(materialcopy)
       If materiallen = 0 Then
            spiritpartmacro = Cells(6 + i, 1).Value
                For a = 1 To (endrow - 5)
                    spiritpartmacro = Cells(6 + a, 1).Value
                        For j = 1 To endrowMaster
                            masterusedin = Worksheets("Master").Cells(1 + j, 3).Value
                            masterusedlen = Len(masterusedin)
                            If masterusedlen > 5 Then
                                For g = 1 To (masterusedlen - 5)
                                    midused = Mid(masterusedin, g, 5)
                                        If spiritpartmacro = midused Then
                                            materialcopy = Worksheets("Master").Cells(1 + j, 1).Value
                                            Cells(6 + a, 3).Value = materialcopy
                                        End If
                                Next g
                            End If
                                ''''need to count matches
                                    ''''need to insert number of rows equal to quantity of matches below Cells(6 + i, 1).value on activeworksheet
                                    ''''need to copy over cells(1 + j, 1).value for each match into the empty rows
                        Next j
                Next a
        End If
Next i

If none of this makes sense, I apologize and can try to explain better or provide an example of the cell manipulation I am hoping for.