Excel Tab Name: Rule

Cell A1 contains this rule.

“DSO_90Day”->”FA_NoFunc”->”FCCS_No Intercompany”->”FCCS_Data Input”->”FCCS_No Movement”->”NoCC”->”FCCS_YTD_Input” = ((“TradeAR”->”Total Functional Area”->”FCCS_Intercompany Top”->”FCCS_Total Data Source”->”FCCS_Movements”->”Total Business Unit”->”FCCS_Periodic” – “TradeARTooling”->”Total Functional Area”->”FCCS_Intercompany Top”->”FCCS_Total Data Source”->”FCCS_Movements”->”Total Business Unit”->”FCCS_Periodic” + “AllowDbtflAcc”->”Total Functional Area”->”FCCS_Intercompany Top”->”FCCS_Total Data Source”->”FCCS_Movements”->”Total Business Unit”->”FCCS_Periodic” ) / (“NetSalesTwoMthsPrior”->”Total Functional Area”->”FCCS_Intercompany Top”->”FCCS_Total Data Source”->”FCCS_Movements”->”Total Business Unit”->”FCCS_Periodic” + “NetSalesCurrent”->”Total Functional Area”->”FCCS_Intercompany Top”->”FCCS_Total Data Source”->”FCCS_Movements”->”Total Business Unit”->”FCCS_Periodic” + “NetSalesPrior”->”Total Functional Area”->”FCCS_Intercompany Top”->”FCCS_Total Data Source”->”FCCS_Movements”->”Total Business Unit”->”FCCS_Periodic”)) * 90;

I’m trying to parse this rule with this Macro and get it into this format within a tab called SmartView.

Expected output

Sub ParseRule()
    Dim ruleSheet As Worksheet
    Dim smartViewSheet As Worksheet
    Dim rule As String
    Dim parts() As String
    Dim i As Integer
    Dim cellValue As Variant
    Dim operators As String
    Dim currentRow As Integer
    Dim currentCol As Integer
    Dim members() As String
    Dim remainingRule As String
    Dim operatorFound As Boolean
    Dim nextOperatorIndex As Integer
    Dim nextOperator As String
    
    ' Define the sheets
    Set ruleSheet = ThisWorkbook.Sheets("Rule")
    Set smartViewSheet = ThisWorkbook.Sheets("SmartView")
    
    ' Clear previous content in SmartView sheet
    smartViewSheet.Cells.Clear
    
    ' Get the rule from A1 in Rule sheet
    rule = ruleSheet.Range("A1").Value
    
    ' Replace all double quotes with empty string
    rule = Replace(rule, """", "")
    
    ' Define operators to split the rule
    operators = "=+-*/()"
    
    ' Initialize starting row and column
    currentRow = 1
    currentCol = 1
    
    ' Initialize remainingRule with the rule
    remainingRule = rule
    
    ' Loop through the rule
    Do While Len(remainingRule) > 0
        ' Find the next operator and its position
        operatorFound = False
        nextOperatorIndex = Len(remainingRule) + 1
        nextOperator = ""
        
        For i = 1 To Len(operators)
            Dim op As String
            op = Mid(operators, i, 1)
            
            Dim pos As Integer
            pos = InStr(remainingRule, op)
            
            ' Check if the operator is "-" and if it's not part of "->"
            If op = "-" Then
                Dim nextArrow As String
                nextArrow = "->"
                If InStr(remainingRule, nextArrow) = 0 Then
                    If pos > 0 And pos < nextOperatorIndex Then
                        nextOperatorIndex = pos
                        nextOperator = op
                        operatorFound = True
                    End If
                End If
            Else
                If pos > 0 And pos < nextOperatorIndex Then
                    nextOperatorIndex = pos
                    nextOperator = op
                    operatorFound = True
                End If
            End If
        Next i
        
        ' Process the part before the next operator
        If nextOperatorIndex > 1 Then
            parts = Split(Left(remainingRule, nextOperatorIndex - 1), ">")
            For Each cellValue In parts
                smartViewSheet.Cells(currentRow, currentCol).Value = Trim(cellValue)
                currentCol = currentCol + 1
            Next cellValue
        End If
        
        ' Process the operator
        If operatorFound Then
            smartViewSheet.Cells(currentRow, currentCol).Value = nextOperator
            currentRow = currentRow + 1
            currentCol = 1
        End If
        
        ' Move to the remaining part of the rule
        remainingRule = Mid(remainingRule, nextOperatorIndex + 1)
    Loop
    
    ' Handle any remaining part after the last operator
    If Len(remainingRule) > 0 Then
        parts = Split(remainingRule, ">")
        For Each cellValue In parts
            smartViewSheet.Cells(currentRow, currentCol).Value = Trim(cellValue)
            currentCol = currentCol + 1
        Next cellValue
    End If
End Sub

However, I’m close but no cigar. Could use another pair of eyes on this.

Current Results