Can anyone tell me how to count nested if statements in a formula via VBA.
That is I want to be able to go into a cell and assess that the formula below
has three IF stmts

=IF(XXXXXXX,IF(XXXXXXX, IF(XXXXXX)))

This may be a Regular Expression question.

Thanks

ExcelMonkey,
How about:

Public Function CountFunction(TargetCell As Range, FunctionName As String)
As Long
Dim Temp As Variant
Temp = Split(UCase(TargetCell.Formula), UCase(FunctionName) & "(")
CountFunction = UBound(Temp)
If CountFunction < 1 Then CountFunction = 0
End Function

Unless you have other instance(s) of "if(" in the formula.

NickHK

"ExcelMonkey" wrote in message
...
Can anyone tell me how to count nested if statements in a formula via VBA.
That is I want to be able to go into a cell and assess that the formula
below
has three IF stmts

=IF(XXXXXXX,IF(XXXXXXX, IF(XXXXXX)))

This may be a Regular Expression question.

Thanks

Just as a point of interest, your example formula has two nested IF
statements (two are nested within another IF statement). You can have
up to seven nested within another IF statement (total of 8 IFs).

Regards

Paul Martin
Melbourne, Australia

Hi. This small demo returns 3. Not sure if this covers all situations
though.

Sub Demo()
Dim s, t
Dim n As Long

'// Put formula in B1
[B1].Formula = "=IF(A1=1,10,IF(A1=2,20, IF(A1=3,30)))"

'// Then...
s = [B1].Formula
t = Replace(s, "IF(", vbNullString)
n = (Len(s) - Len(t)) / 3
Debug.Print n
End Sub

HTH
--
Dana DeLouis
Win XP & Office 2003


"ExcelMonkey" wrote in message
...
Can anyone tell me how to count nested if statements in a formula via VBA.
That is I want to be able to go into a cell and assess that the formula
below
has three IF stmts

=IF(XXXXXXX,IF(XXXXXXX, IF(XXXXXX)))

This may be a Regular Expression question.

Thanks