IF Statement Parsing

Eldrod

Board Regular
Joined
Mar 11, 2010
Messages
56
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!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
This'll parse all your IF's in column A

Code:
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

Board Regular
Joined
Mar 11, 2010
Messages
56
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,222
Messages
5,594,904
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top