Remove If Statement From Cell Leaving [Value_If_False] From Formula

[dopeybob435]

I need to figure out a VBA that will search the sheets for all formulas containing If statements and delete everything except for the final clause of the formula

[Per_Hour_Wage is a named range]
=IF(ISNUMBER(Per_Hour_Wage)=FALSE,"#Wage!",IF(ISNUMBER(C16)=FALSE,C16,(C16*Per_Hour_Wage)))
Would produce
=(C16*Per_Hour_Wage)

I can't have it just select the right x characters in the formulas since the length of the relevent formula will change with each cell. Possibly search for length to the right of the last comma then return that part of the formula?

Elsewhere in this VBA I will have it copy the relevent sheets to a new file then it will need to perform this proceedure before I can send this out to auditors.




[Background] Need to create a workbook for auditors that will simplify the formulas I am using in my sheets. This will be similar to a values only copy Except it will still have working formulas, just immensely simplified. The workbook has formulas throughout it that include nested if statements that will throw up a custom error message (#Wage!, #Weeks!) that will quickly show me which data I am missing from the calculation, this prevents sending out partial data. But those who will be auditing the data have become confused with those being included in what I send them.

[mat.davies]

Hi

This is an interesting one! I've had a go, not quite sure it's what you're after but it shouldn't be too hard to change. At the moment, it works for your example above, but i'm not sure how robust it is.

Please Login or Register  to view this content.

give me a shout if there are any issues

Cheers

Mat

[dopeybob435]

Its throwing the compile error for the "End If" without the starting "If", If I remove that "End If" to test it errors out on the firstcomma= line.
When you're dimensioning is "ifStmntLoc" supposed to be "lastIFloc"?

Thanks
David

[dopeybob435]

The code you posted looks like it would do whats needed. I just need some help getting it to run, if you could clarify the "End If"

[jindon]

Try

Please Login or Register  to view this content.

[dopeybob435]

Thank you,
Jindon your code works in the example very well (and fast). When I try it in another file, which I pulled the example from, it throws a Run-time error '1004' on you line: r.Formula = "=" & .Replace(r.Formula, "$1")
Any ideas on why this would work in the example but does not in the other?

Thanks
David

[dopeybob435]

Thank you,
Jindon your code works in the example very well (and fast). When I try it in another file, which I pulled the example from, it throws a Run-time error '1004' on you line: r.Formula = "=" & .Replace(r.Formula, "$1")
Any ideas on why this would work in the example but does not in the other?

Thanks
David

[jindon]

I can't say anything about the file that I don't know.