Forecast KIT splitting based on BOM list

[SPet]

Hi All,

I'm not familiar with Excel VBA macros...

I have a forecast sheet (Sheet1) and a BOM list other sheet (Sheet2).
The forecast sheet contains the item codes in the first (A) column and the forecasted quantity by week per column.
The BOM list sheet contains the KIT item code in the first (A) column, KIT description in column B , Component code in column C and Component description in column D.
The forecast item codes contains simple products and kits.
The KITs could be included one to multiple components.
I would like to use macro for the following process:
- search the item code from the beginning of the forecast sheet on the BOM sheet if finds a match (means this item is a KIT) insert a row or insert row below the item code on sheet1 and insert the component code(s) from the sheet2 column C to this(these) rows and copy the quantity of the KIT (the KIT row is above these new rows) to this(these) rows.
This should run row by row until find an empty cell.

I tried several sample codes from Excel Forum for example this:

Option Explicit
Public Sub PHI()
    Dim myRange
    Sheets("Sheet1").Activate
    Range("A1").Activate
    Sheets("Sheet2").Activate
    Range("A1").Activate
    While Not IsEmpty(ActiveCell.Value)
        With Sheets("Sheet1").Range("A1", _
                                    "A" & ActiveCell.SpecialCells(xlLastCell).Row)
            Set myRange = .Find(ActiveCell.Value, LookIn:=xlValues)
            If Not myRange Is Nothing Then
                myRange.Offset(0, 7).Value = ActiveCell.Offset(0, 7).Value
            End If
        End With
        ActiveCell.Offset(1, 0).Activate
    Wend
End Sub

I 've modified the above code but it is find and write on the active sheet only not write on the other.
And I can insert row only above of the cell but I need it(them) below of the cell.

I'm using Excel 2007.
I have attached the sample.
Forecast_BOM.xlsx

Do you have any ideas?

Thanks in advance.

Peter