Identification and highlighting of interdependent materials within a bill of materials (BOM)

3005mfab 

To identify and highlight all rows that are dependent on a specific material number within a Bill of Materials (BOM), you can use a macro. This macro will traverse the hierarchy and highlight all rows that are dependent on a specific material. Here is how you can do this:

1. Prepare your Data: Ensure your BOM dataset is structured with columns for Level, Pegged Requirement, and Material. Here’s an example structure:

2. Create the Macro: Open the VBA editor by pressing Alt + F11, insert a new module by right-clicking on any existing module and selecting Insert > Module, and paste the following VBA code:

Vba Code is untested backup your file.

Sub HighlightDependencies()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim searchMaterial As String
    Dim i As Long
    Dim dependentMaterials As Collection
    Dim highlightedMaterials As Collection
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    
    ' Define the search material
    searchMaterial = InputBox("Enter the material number to search for dependencies:", "Search Material")
    
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize collections
    Set dependentMaterials = New Collection
    Set highlightedMaterials = New Collection
    
    ' Start with the search material
    Call AddDependencies(ws, searchMaterial, lastRow, dependentMaterials, highlightedMaterials)
    
    ' Highlight the rows
    For i = 1 To highlightedMaterials.Count
        ws.Rows(highlightedMaterials(i)).Interior.Color = RGB(255, 255, 0) ' Yellow color
    Next i
    
    MsgBox "Highlighting completed!"
End Sub

Sub AddDependencies(ws As Worksheet, material As String, lastRow As Long, dependentMaterials As Collection, highlightedMaterials As Collection)
    Dim i As Long
    Dim peggedRequirement As String
    Dim subMaterial As String
    
    ' Loop through the rows to find dependencies
    For i = 2 To lastRow
        peggedRequirement = ws.Cells(i, 2).Value
        subMaterial = ws.Cells(i, 3).Value
        
        ' Check if the current row is dependent on the material
        If peggedRequirement = material Then
            ' Add the row to the highlighted collection if not already added
            On Error Resume Next
            If IsError(Application.Match(i, highlightedMaterials)) Then
                highlightedMaterials.Add i
            End If
            On Error GoTo 0
            
            ' Add the sub-material to the dependent collection if not already added
            On Error Resume Next
            If IsError(Application.Match(subMaterial, dependentMaterials)) Then
                dependentMaterials.Add subMaterial
                ' Recursively find dependencies for the sub-material
                Call AddDependencies(ws, subMaterial, lastRow, dependentMaterials, highlightedMaterials)
            End If
            On Error GoTo 0
        End If
    Next i
End Sub

3. Run the Macro:

• Close the VBA editor.
• Press Alt + F8 to open the Macro dialog box.
• Select HighlightDependencies and click Run.

Explanation of the Code:

• HighlightDependencies Subroutine:

• Prompts the user to input the material number to search for dependencies.
• Initializes collections to keep track of dependent materials and rows to highlight.
• Calls the AddDependencies subroutine to populate the collections.
• Highlights the rows in yellow where dependencies are found.

• AddDependencies Subroutine:

• Recursively finds all rows that depend on the given material and adds them to the collections.
• Ensures each dependent material and row is only added once to avoid duplication.

Customization:

• Change "Sheet1" to the actual name of your worksheet.
• Adjust the columns if your data is not in columns A, B, and C.
• Modify the highlight color by changing the RGB(255, 255, 0) value to any other RGB color if desired.

This macro will help you identify and highlight all rows in your BOM that are dependent on a specific material number, making it easier to comprehend the complete dependency chain of any material. The text, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.