IF Statement Parsing

[Eldrod]

Anyone have any code that can be used to parse an IF statement read from a cell into its 3 parts (condition test, true case, false case)? I'm not really wanting to have to write that from scratch.

Any help is greatly appreciated!

 

[Weaver]

This'll parse all your IF's in column A

Sub parseIf()
    Dim lr, i, a, b
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To lr
        a = Cells(i, "A").Formula
        If Left(a, 3) = "=IF" Then
            a = Mid(a, 5, Len(a))
            a = Split(Left(a, Len(a) - 1), ",")
            For b = 0 To 2
                Cells(i, b + 2) = a(b)
            Next
        End If
    Next i
End Sub

Not sure why you'd want to, tho....

 

[Eldrod]

Thanks, but I'm not sure that'll work in cases where I might have nested IFs or embedded commas.

I'm writing a macro to validate the formulas in a spreadsheet by locating any division statements and testing to make sure these cannot generate a #DIV/0 error. For example if I find A/X, then I modify the formula to =IF(X,A/X,0). The problem is when I find the "/" in an IF statement or if I have already fixed the code like the example above. I don't want to fix it again as it no longer can generate a #DIV/0 error. I only need to parse out the highest level IF, but what could be inside that IF could be almost anything.

So the character-by-character processing has to track commas, parenthesis, and quotes and the sequence in which they occur to locate the correct comma that delineates the statement. I'm not looking forward to writing that unless I have to...

 

[Weaver]

I'm not sure how you'd display this but it might be possible to call the routine on itself (recursion) for each of the 3 separate elements to pick up nested ifs. The code would need a bit of a rejig and a lot depends on what you want to do with the results.