CODEWITHSUNDEEP
excelvbaexcel-formula
Hightower
Hightower

Reputation: 1008

Excel Grouped Columns check if children contain data

I have a spreadsheet that contains a list of e.g software products, some products go up to module level and others are just a product. I have a grouping that groups each vendors products or product modules...

the excel sheet aims to map vendor products (or modules if they exist) to certain functions. an "X" in the cell indicates that the function is supported. in the picture module A1.1 supports function 1. ... and Product A2 (with no defined modules) also supports Function 1.

The problem comes in when working up the "tree" of grouped columns... i need a sub/function that will complete the rest of the mapping. ie... if i check both cell D2 and E2 i would like to run a function that updates cell C2 to an X and then Cell B2 to an X. (the X would state that all modules support the function)

So in the graphic, the red cells were manually entered, and the non-red "X" and "O" cells would be added automatically.

I know this seems lazy asking in this format, however i would appreciate help even to get directed in the right idea, brain is fried and i cant even think how to solve this...

enter image description here

Upvotes: 0

Views: 1433

Answers (1)

IronicJ
IronicJ

Reputation: 101

You can use the OutlineLevel Property located in the Columns to locate the parents and the childrens based on the worksheet outline logic.

Try:

'This function goes thru the outline childrens of a cell and can apply some logic based on their value
Function SubComponentsPresent() As String
    Application.Volatile

    Dim RefRange As Range
    Set RefRange = Application.Caller

    Dim Childrens As Range
    Set Childrens = OutLineChildren(RefRange)

    Dim oCell As Range
    For Each oCell In Childrens
        '-----------
        'Insert code here
        '-----------
    Next oCell

    SubComponentsPresent = tOut
End Function

'This functions returns the childrens of a cell (Considering a column outLine)
Function OutLineChildren(RefCell As Range) As Range
    Dim oCell As Range
    Dim tOut As String

    With RefCell.WorkSheet
        If .Outline.SummaryColumn = xlSummaryOnRight Then
            Set oCell = RefCell.Offset(0, -1)
            Do Until oCell.EntireColumn.OutlineLevel <= RefCell.EntireColumn.OutlineLevel
                If oCell.EntireColumn.OutlineLevel = RefCell.EntireColumn.OutlineLevel + 1 Then
                    If tOut <> "" Then tOut = tOut & ","
                    tOut = tOut & oCell.Address
                End If
                Set oCell = oCell.Offset(0, -1)
            Loop
        Else
            Set oCell = RefCell.Offset(0, 1)
            Do Until oCell.EntireColumn.OutlineLevel <= RefCell.EntireColumn.OutlineLevel
                If oCell.EntireColumn.OutlineLevel = RefCell.EntireColumn.OutlineLevel + 1 Then
                    If tOut <> "" Then tOut = tOut & ","
                    tOut = tOut & oCell.Address
                End If
                Set oCell = oCell.Offset(0, 1)
            Loop
        End If
    End With
    Set OutLineChildren = RefCell.Worksheet.Range(tOut)
End Function

Upvotes: 3

Related Questions