Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How Do I create a cascading MS Excel macro

Posted on 2011-04-29
Medium Priority
Last Modified: 2012-05-11
Image an assembly that contains 3 or 4 sub assemblies below it.  Level 1 is the top assembly, level 2-4 go into that top level assembly or part number.  When I export my BOM into an Excel spreadsheet all the part numbers fall below each other and I need them to cascade down according to the level they go into the part number above it.  My current BOM is made up of 1150 different part numbers or lines.  Those 1150 part numbers make up a few hundred assemblies.  I need a macro that will take the spreadsheet and cascade the part numbers by their assembly level.  Clear as mud lol?  Let me know if I need to explain further.  Thanks
Question by:mcrouch1
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35493266
You can help by uploading a sample excel file

Author Comment

ID: 35493783
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35493818
The numbers are already cascading. Do you want the cascading in different columns?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 35494063
Yes in different columns per the part numbers level.  So if its level 1 then have the part number in column 1, level 2 column 2, level 3 column 3, etc....Sorry, I didn't use the correct terminology.

Author Comment

ID: 35494073
Not just for this spreadsheet but for any report like this that gets exported into Excel.
LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 35494291
One easy way is to use the Text-to-columns feature of excel.

For this first you would have to unmerge any cells in this region. Select the entire region and a few columns to the right also. press Ctrl-1, select alignment tab and uncheck "Merge cells"

Then select the one column which contains the numbers
Go to Data > Text to columns
select delimited
type a period (.) in the "other" box
and click on finish.

LVL 19

Expert Comment

by:Richard Daneke
ID: 35494785
Saqib keystrokes can be saved and re-used as a macro.
LVL 42

Expert Comment

ID: 35500161
Not sure whether type or sequence comes before the levels or after.  I put them up front.

Here's the code:
Option Explicit
Type RowData
    lvl As Integer
    typ As String
    seq As Integer
    part As String
End Type

Sub GenerateCascadeOutput()
Dim mySheet As Worksheet
Dim rowProcess As RowData
Dim myRow As Range
Dim outCursor As Range
Dim outSheet As Worksheet
Dim maxLevels As Long, maxRange As Range, i As Integer
Dim WS As Worksheet
Dim chkRng As Range
Dim partLevels() As String, j As Integer
Dim xMsg As Long

    Set WS = Sheets("Import_File")
    On Error Resume Next
    Set outSheet = Sheets("Output_DB")
    outSheet.Name = "Output_DB"
    If Err.Number = 0 Then
        xMsg = MsgBox("Overwrite Output_DB?", vbYesNo, "Hit Yes to Overwrite")
        If xMsg = vbYes Then
            Application.DisplayAlerts = False
            Application.DisplayAlerts = True
            Exit Sub
        End If
        Set outSheet = Sheets.Add(after:=Sheets(Sheets.Count))
        outSheet.Name = "Output_DB"
    End If
    On Error GoTo 0

    Set maxRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
    maxLevels = Evaluate("MAX(" & maxRange.Address & ")")
    ReDim partLevels(maxLevels) As String
    outSheet.Range("A1:B1").Value = Array("TYPE", "SEQUENCE") 'use this if type/sequence come first
    Set outCursor = outSheet.Range("A2")
    For i = 1 To maxLevels
        outSheet.Cells(1, 3 + i - 1).Value = "LEVEL " & i 'use this if type/sequence come first
        'outSheet.Cells(1, i).Value = "LEVEL " & i 'comment out if type/sequence come second
    Next i
    'outSheet.Cells(1, maxLevels + 1).Resize(1, 2).Value = Array("TYPE", "SEQUENCE") 'comment out if type/sequence come second
    'minor changes, below, if type/sequence come second
    For Each myRow In maxRange.EntireRow
        If IsNumeric(myRow.Cells(1, 1).Value) Then
            If myRow.Cells(1, 1).Value > 0 Then
                rowProcess.lvl = myRow.Cells(1, 1).Value
                rowProcess.typ = myRow.Cells(1, 3).Value
                rowProcess.seq = myRow.Cells(1, 4).Value
                rowProcess.part = Replace(myRow.Cells(1, 5).Value, ".", "")
                outCursor.Value = rowProcess.typ
                outCursor.Offset(0, 1).Value = rowProcess.seq
                outCursor.Offset(0, 2 + rowProcess.lvl - 1).Value = rowProcess.part
                'now look up and set
                For j = rowProcess.lvl - 1 To 1 Step -1
                    outCursor.Offset(0, 2 + j - 1).Value = outCursor.Offset(-1, 2 + j - 1).Value
                Next j
                Set outCursor = outCursor.Offset(1, 0)
                'do nothing
            End If
        End If

    Next myRow
    Range(Cells(1, 1), Cells(Rows.Count, maxLevels + 2)).AutoFilter

End Sub

Open in new window

See attached file demonstrating the output



Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question