Determine Parent Child

Question

Hi All.

I've created VBA Macro in Excel to provide the Indent value in col B (from Alignment tab of the Format Cell screen) and trying to establish Parent/Child in Col C. The output should match Col D, but having a hard time getting the logic in. It has to look at Col B to build Parent/Child as Col A could be different values at times.

Any help would be GREATLY appreciated!

Sub GetIndentLevels()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim rng As Range
Dim indentLevel As Integer

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Target") ' Change "Sheet1" to your sheet name

' Find the last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

' Loop through each row in column A
For i = 1 To lastRow
    ' Get the range for the current cell in column A
    Set rng = ws.Cells(i, "A")
    
    ' Get the indent level of the cell
    indentLevel = rng.indentLevel
    
    ' Write the indent level to column B
    ws.Cells(i, "B").Value = indentLevel
Next i
End Sub

Sub DetermineParentChild()
Dim ws As Worksheet
Dim lastRow As Long
Dim currentRow As Long
Dim currentValue As Variant
Dim nextValue As Variant
Dim markParent As Boolean

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Target")

' Find the last row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

' Initialize the markParent variable
markParent = False

' Loop through each row in column A
For currentRow = 1 To lastRow
    ' Get the value of column B for the current row
    currentValue = ws.Cells(currentRow, 2).Value
    
    ' Check if the current value is numeric
    If IsNumeric(currentValue) Then
        ' Check if the next row exists and get its value
        If currentRow < lastRow Then
            ' Get the value of column B for the next row
            nextValue = ws.Cells(currentRow + 1, 2).Value
        Else
            ' If there is no next row, set nextValue to a default value
            nextValue = ""
        End If
        
        ' Check if the current value should be marked as "P" (Parent) or "C" (Child)
        If markParent Then
            ws.Cells(currentRow, 3).Value = "P"
        Else
            If nextValue = currentValue + 1 Then
                ws.Cells(currentRow, 3).Value = "P"
            Else
                ws.Cells(currentRow, 3).Value = "C"
            End If
        End If
        
        ' Update the markParent variable for the next iteration
        markParent = (nextValue <> currentValue + 1)
    Else
        ' If the current value is not numeric, mark it as "C" (Child)
        ws.Cells(currentRow, 3).Value = "C"
    End If
Next currentRow
End Sub

Answer 1

• Your code is fairly close to finish. You'll get the expected output by removing the logic of markParent.

Option Explicit

Sub DetermineParentChild()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim currentValue As Variant
    Dim nextValue As Variant
    Dim markParent As Boolean
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets(1)
    ' Find the last row in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Initialize the markParent variable
    markParent = False
    ' Loop through each row in column A
    For currentRow = 2 To lastRow  ' ** start from row 2
        ' Get the value of column B for the current row
        currentValue = ws.Cells(currentRow, 2).Value
        ' Check if the current value is numeric
        If IsNumeric(currentValue) Then
            ' Check if the next row exists and get its value
            If currentRow < lastRow Then
                ' Get the value of column B for the next row
                nextValue = ws.Cells(currentRow + 1, 2).Value
            Else
                ' If there is no next row, set nextValue to a default value
                nextValue = ""
            End If
            ' Check if the current value should be marked as "P" (Parent) or "C" (Child)
            ' ***
            If nextValue = currentValue + 1 Then
                ws.Cells(currentRow, 3).Value = "P"
            Else
                ws.Cells(currentRow, 3).Value = "C"
            End If
            ' ***
        Else
            ' If the current value is not numeric, mark it as "C" (Child)
            ws.Cells(currentRow, 3).Value = "C"
        End If
    Next currentRow
End Sub