Forecast KIT splitting based on BOM list

**SPet** · 10-12-2012, 03:37 AM

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

**jindon** · 10-12-2012, 03:55 AM

1) You need to edit your post and wrap the code with the code tag.
It is a MUST rule here
e.g
[code]
Your code here
[/code]

2) How do you want the result? If you upload the file with your desired result from the data provided, it will be clearer.

**SPet** · 10-12-2012, 04:14 AM

1. Sorry.
Modified the post based on the rule.
2. The attached file sheet1 47-49 rows show a sample. If the KIT code (422203923314) find in the KIT list (sheet2) then the components code (422200252183, 422200272624) copied(inserted) below the kit code(sheet1) and the kit forecasted quantity copied to the inserted components rows. I inserted the two components code above the KIT code and copied the quantity manually.
Forecast_BOM_1.xlsx

**jindon** · 10-12-2012, 04:44 AM

Not sure if this is what you wanted.

Sub test()
    Dim r As Range, i As Long, dic As Object, x As Range
    Set dic = CreateObject("Scripting.Dictionary")
    Application.ScreenUpdating = False
    With Sheets("sheet2").Range("a3").CurrentRegion
        For Each r In .Columns(1).Cells
            If Not dic.exists(r.Value) Then
                Set dic(r.Value) = r(, 3)
            Else
                Set dic(r.Value) = Union(dic(r.Value), r(, 3))
            End If
        Next
    End With
    With Sheets("sheet1").Range("a1").CurrentRegion
        For i = .Rows.Count To 2 Step -1
            If dic.exists(.Cells(i, 1).Value) Then
                Set x = dic(.Cells(i, 1).Value)
                With .Rows(i)
                    .Offset(1).Resize(x.Count).Insert
                    .Copy .Offset(1).Resize(x.Count)
                    x.Copy .Offset(1).Cells(1)
                End With
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

**SPet** · 10-12-2012, 06:56 AM

It is working properly.
This is what I need exactly.
Thx.

**jindon** · 10-12-2012, 06:59 AM

OK and thanks for the edit (code tag)

Now please mark this thread as "Solved".

Forecast KIT splitting based on BOM list

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Forecast KIT splitting based on BOM list

Re: Forecast KIT splitting based on BOM list

Re: Forecast KIT splitting based on BOM list

Re: Forecast KIT splitting based on BOM list

Re: Forecast KIT splitting based on BOM list

Re: Forecast KIT splitting based on BOM list

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions