Macro to count IF's in formulas?

[billfinn]

Good morning,
I've run into a bit of a snag with some cells that use IF nested as much as 24 levels deep. So far they haven't angered the Excel 2000 gods but that won't last. Does anyone have any advice for a macro to count the number of times IF occurs in each formula and report the cell location of the ones with more than 7 IF's in the formula? It's beyond my skills.
Very grateful for any assistance.

Thanks,
Bill

 

[shg]

It's more than IFs, right? It's nesting of any combination of functions at 8 or more levels.

 

[billfinn]

SHG
That's correct. If I could just pull the formulas with more than 7 IFs along with their cell coordinates into a separate sheet I could review them to determine if they are nested or not. I know tthat all of the nested functions in this workbook are IF AND and IF OR. Nothing else is nested.
Any thoughts on how to do this?
Thanks much
Bill

 

[billfinn]

Actually I just need to know for one specific worksheet.

 

[njimack]

Give this a whirl...

Sub List_IF()
Dim c As Range
Dim ws As Worksheet

Application.ScreenUpdating = False
Set ws = ActiveSheet
Sheets.Add(After:=ActiveSheet).Name = "IF"

For Each c In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
    If (Len(c.Formula) - Len(Replace(c.Formula, "IF(", ""))) / 3 > 7 Then Sheets("IF").Cells(Rows.Count, 1).End(xlUp).Offset(1) = c.Address
Next c
Application.ScreenUpdating = True
End Sub

 

[billfinn]

Thanks much njimack!
That gives me the cell address which is a great start, it would be very helpful to have the actual formula pasted in the cell next to the cell address.
I tried adding this line at the end of the If statement

:[COLOR=#333333]Sheets("IF").Cells(Rows.Count, 1).End(xlUp).Offset(2) = c.formula[/COLOR]

but it didn't work.
I'm not sure how to get the actual formula to paste somewhere as text vs the value
Thanks,
Bill

 

[Fluff]

Try

Then Sheets("IF").Cells(Rows.Count, 1).End(xlUp).Offset(1).resize(,2).value = array(c.Address,Replace(c.Formula, "=", ""))

 

[billfinn]

Fluff,
Thanks a million! That gave me exactly what I need.
Thanks,
Bill

 

[Fluff]

Glad we could help & thanks for the feedback