Creating a macro that wraps a formula around an existing formula

[Big Ry]

I have a spreadsheet with hundreds of individual calculations. I want to make it so all of those cells will be "-" if one particular cell is blank. The standard excel function I'm using if I do this manually is =IF(W10="","-",). Obviously modifying a cell formula hundreds of times is not practical. I tried looking up macros online that fit my need, but I can't seem to find one that has a cell reference within it (W10). I found an example for ISERROR, but you do not need to reference another cell using that macro. I tried over and over to modify that macro to work with my situation, but I keep getting runtime errors. When I hover over the individual components of the output in VBA, they appear to be correct. It's just that the final output errors out. It says Error 2015, if that means anything to you. My lasted attempt at the coding is below. [CODE]Sub IfDash() 'PURPOSE: Add an IF() Function around all the selected cells' formulas to output "-" if W10 is blank. Dim rng As Range Dim cell As Range Dim x As String Dim y As String 'Determine if a single cell or range is selected If Selection.Cells.Count = 1 Then Set rng = Selection If Not rng.HasFormula Then GoTo NoFormulas Else 'Get Range of Cells that Only Contain Formulas On Error GoTo NoFormulas Set rng = Selection.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 End If 'Loop Through Each Cell in Range and add =IF(W10="", "-",[formula without "="]) For Each cell In rng.Cells x = cell.Formula y = Right(x, Len(x) - 1) cell = "=IF(R10C23="""",""-""," & y & ")" Next cell Exit Sub 'Error Handler NoFormulas: MsgBox "There were no formulas found in your selection!" End Sub[/CODE]

 

[Trebor76]

Hi Big Ry,

Welcome to MrExcel!!

Try this:

Option Explicit
Sub IfDash()
    'PURPOSE: Add an IF() Function around all the selected cells' formulas to output "-" if W10 is blank.

    Dim rng As Range
    Dim rngMyCell As Range
    Dim x As String
         
    Set rng = Selection
    If Not rng.HasFormula Then GoTo NoFormulas
    On Error GoTo NoFormulas
        Set rng = Selection.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
     
    'Loop Through Each Cell in Range and add =IF(W10="", "-",[formula without "="])
    For Each rngMyCell In rng.Cells
        x = Mid(rngMyCell.Formula, 2, Len(rngMyCell.Formula) - 1) 'Remove leading equal sign
        rngMyCell.Formula = "=IF(Len(W10)=0,""-""," & x & ")"
    Next rngMyCell
     
    Exit Sub
     
    'Error Handler
NoFormulas:
      MsgBox "There were no formulas found in your selection!"
 
End Sub

Regards,

Robert

 

[Big Ry]

That gives me a Run-time error '5': Invalid procedure call or argument

on this line:

x = Mid(rngMyCell.Formula, 2, Len(rngMyCell.Formula) - 1) 'Remove leading equal sign

 

[Trebor76]

Not sure as it works for me

What's in the cell when error is generated?

One change is to ignore cell W10 or else it will create a circular reference:

Option Explicit
Sub IfDash()
    'PURPOSE: Add an IF() Function around all the selected cells' formulas to output "-" if W10 is blank.

    Dim rng As Range
    Dim rngMyCell As Range
    Dim x As String
         
    Set rng = Selection
    If Not rng.HasFormula Then GoTo NoFormulas
    On Error GoTo NoFormulas
        Set rng = Selection.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
     
    'Loop Through Each Cell in Range and add =IF(W10="", "-",[formula without "="])
    For Each rngMyCell In rng
        If rng.Address <> "$W$10" Then
            x = Mid(rngMyCell.Formula, 2, Len(rngMyCell.Formula) - 1) 'Remove leading equal sign
            rngMyCell.Formula = "=IF(Len(W10)=0,""-""," & x & ")"
        End If
    Next rngMyCell
     
    Exit Sub
     
    'Error Handler
NoFormulas:
      MsgBox "There were no formulas found in your selection!"
 
End Sub

Regards,

Robert

 

[Big Ry]

I entered VBA, copied/pasted what you wrote, set the shortcut to Ctrl+Shift+Q.

Just now I tried it on cell P169, which currently has "=(S141*$G$55)/$G$54" in it. Using the shortcut does nothing. Opening the Macros dialog and clicking 'Run' gives me the runtime error. I get the same response when selecting multiple cells.

 

[Trebor76]

Still OK on my side?? What's in cell W10?

 

[Big Ry]

At the moment, nothing. Its completely empty.

 

[Big Ry]

Is there maybe some setting that I need to have on that might be causing this?

 

[Trebor76]

At the moment, nothing. Its completely empty.

That's not the issue then as mine was empty too.

Is there maybe some setting that I need to have on that might be causing this?

No. The only thing I can think of is that the length argument of this line of code...

x = Mid(rngMyCell.Formula, 2, [B]Len(rngMyCell.Formula)[/B] - 1) 'Remove leading equal sign

...is returning zero so when 1 is tried to be subtracted from it, it throws the error.

If can post your file on a sharing site like www.box.com I [I]may[/I] be able to shed some more insight.

Robert

 

[Big Ry]

I went to save the file to make sure the macro is saved, and I was prompted that my file was a "macro-free" workbook. So I saved a copy as a macro-enabled workbook and uploaded both below.

https://www.dropbox.com/s/px3008jb1hf2egr/Girder 12 Splice Constructability Check_Macro.xlsm?dl=0

https://www.dropbox.com/s/drujtx2mjx9eedj/Girder 12 Splice Constructability Check.xlsx?dl=0

In the macro-enabled workbook I get "Invalid procedure call or argument" when trying to use the shortcut keys. If I open the Macros dialog, the macro appears to work properly.