…I am just going through threads now where they use up to the full allowed 64!!
….In desperation I knocked up just now this code to write out a table of the conditions…
.. For fun if you apply it to your and my versions of your original formula you get this, which is a bit more readable
Alan,
Are you sad or wot???
It's comforting to know that I am not alone!!
A nice little idea to break out the nested if's.
A couple of comments......
'Put all the IF's in the same syntax' If you copy the confirmed formula from it's cell to the test cell then will not any IF's have been automatically capitalised? Thus unless you add your prefix and edit any IF's to e.g. If then there is no ned to check?
Also, if you have some text in the formula that includes e.g. "if" it will convert that to "IF" and it becomes an erroneous separator in the Split function.
Another thought is that splitting on IF will incorrectly split at the backend of COUNTIF, SUMIFS etc etc.
Finally, your code lists the breakdown only in column A.
Being a sad b*s!*rd, with nothing better to do than mess with Excel for no good reason other than because 'it's there', I have modified your original code, below.
You can edit one code line, ('********), by having it as code line or comment, to have the formula in the test cell retained as formula, in editable form or converted to text by adding the prefix.
As before you need empty cells below the test cell but the test cell can be in any column.
I may not have covered all bases but the Replace functions are looking for the likes of "=IF(" ",IF(" as valid separation points. Such points then replaced with the .normally, unlikely text "§§" to be used as the Split delimiter.
Code:
Sub SplitIF2() 'Split of long Nested IF
Dim IF_Formula As String, IF_Line() As String 'Formula in String Form,Split at IF lines
Dim IF_Line_Index As Long 'A number for use in Array Index or Cell Row
'**** Put your formula in a cell with enough free cells under it,
IF_Formula = "Split IFs >>> " & ActiveCell.Formula 'adds something before the = to change it to text
'comment out the below line if you wish to retain and be able to edit the formula
'*********
ActiveCell.Value = IF_Formula '*** converts cell formula to text if you wish
'*********
IF_Formula = Replace(Replace(Replace(IF_Formula, ",IF(", ",§§("), "=IF(", "=§§("), "(IF(", "(§§(") 'Put all IFs in same syntax
IF_Line = Split(IF_Formula, "§§") 'Split that formula using §§ as seperator
For IF_Line_Index = 1 To UBound(IF_Line)
ActiveSheet.Cells(ActiveCell.Row + IF_Line_Index, ActiveCell.Column).Value = IF_Line(IF_Line_Index) 'Write under formula the split line
Next IF_Line_Index
End Sub 'SplitIF()
The two approaches compare as below with the sample formula....
| Excel Workbook |
|---|
|
|---|
| A | B |
|---|
| 1 | Doc_Split =IF(B6>B8,"More",IF(B8Split IFs >>> =IF(B6>B8,"More",IF(B8 |
|---|
| 2 | (B6>B8,"More", | (B6>B8,"More", |
|---|
| 3 | (B8(B8 |
|---|
| 4 | ference",COUNT | |
|---|
| 5 | (A6:A9,6))) | |
|---|
|
|---|
