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]