How Do I create a cascading MS Excel macro

Posted on 2011-04-29
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
    You can help by uploading a sample excel file

    Author Comment

    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    The numbers are already cascading. Do you want the cascading in different columns?

    Author Comment

    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

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

    Accepted Solution

    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 18

    Expert Comment

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

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now