VBA Code to Wrap Formula in an =IF()?

Question

Hello,

I'm looking to use VBA to quickly insert the formula in my selected cell(s) in an =IF().

For example, if my cell formula is =[Formula], I want to change it to =IF( ([Formula] = 0), "", [Formula])

This is the code I have so far, but I keep getting a Run-Time Error '1004': Application-defined or object-defined error..

---

Sub Add_IF_Selection()

  Dim myCell As Range    

    For Each myCell In Selection.Cells

      If myCell.HasFormula And Not myCell.HasArray Then           

        myCell.Formula = "=IF(" & myCell.Formula & "=0,""," & myCell.Formula & ")"

      End If

  Next

End Sub

---

I think I'm very close or making a very silly mistake. Anyone know how I can solve this? Thanks!

Answer 1

Hi Nitsuj5,

Try:

'=========>>

Sub Add_IF_Selection()

  Dim myCell As Range

  Dim sStr As String

    For Each myCell In Selection.Cells

      If myCell.HasFormula And Not myCell.HasArray Then

      sStr = Mid(myCell.Formula, 2)

         myCell.Formula = "=IF(" & sStr & "=0,""""," & sStr & ")"

      End If

  Next

End Sub

'<<=========

Note that you need to allow for the initial = sign and you need to double up internal quotes.

===

Regards,

Norman

Answer 2

Hi Nitsuj5,

Thank you for your kind feedback.

===

Regards,

Norman

Answer 3

Thank you very much, it works! I didn't know there was a need to make a new string variable and use Mid().

Answer 4

I apologize as I lost the link to my question in the other forum and I assumed my question didn't get posted, so I asked it here. Won't happen again

Answer 5

Posted under "JHHui"  and answered (twice) in the "Visual Basic for Applications Forum".