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

  • 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 an 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
    End Sub

    Anyone know how to solve this? Thanks!
    Tuesday, July 21, 2015 11:10 PM


All replies

  • Try:

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

    Rod Gill
    Author of the one and only Project VBA Book

    • Proposed as answer by André Santo Wednesday, July 22, 2015 10:51 AM
    • Marked as answer by JHHui Wednesday, July 22, 2015 4:31 PM
    Wednesday, July 22, 2015 12:11 AM
  • Posting the same question in multiple forums in not well received by those whose time you wasted.

    • Edited by James Cone Wednesday, July 22, 2015 12:48 AM duplicate answer
    Wednesday, July 22, 2015 12:20 AM
  • I apologize as I lost the link to this thread and assumed my question wasn't posted. Won't happen again.
    Wednesday, July 22, 2015 4:31 PM
  • Thank you very much, it works! I didn't know there was a need to make a new string variable and use Mid().
    Wednesday, July 22, 2015 4:31 PM