Reputation: 41
I want to write a Macro that checks if a selected section contains formulas and if it does NOT contain a ERRORIF() formula wraps an ERRORIF() around the existing formula.
Everything works fine except for the "check if ERRORIF already exists" part. I do not know how to implement this. (Needless to say I am a VBA beginner)
sep = InputBox("Wähle einen Error-Term für die Wennfehler-Funktion:")
'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 =IFERROR([formula],"")
For Each cell In rng.Cells
If cell.Formula Like "IFERROR" Then
cell = cell
Else
x = cell.Formula
cell = "=IFERROR(" & Right(x, Len(x) - 1) & "," & Chr(34) & sep & Chr(34) & ")"
End If
Next cell
The "Like IFERROR" part should implement this but it does not work, please help.
Upvotes: 0
Views: 31
Reputation: 3034
As you want to see if there is not iferror then apply it, use it as the first option in the if condition and you dont have to check for other cells which are starting with iferror.. I only have one doubt though; what if the cell formula starts with iferror but does not end with ",sep)". In that case you can choose to use
If Left(cell.Formula,8) <> "=IFERROR" AND Right(cell.Formula,len(sep)+4) <> "," & Chr(34) & sep & Chr(34) & ")" Then
For Each cell In rng.Cells
If Left(cell.Formula,8) <> "=IFERROR" Then
x = cell.Formula
cell.Formula = "=IFERROR(" & Right(x, Len(x) - 1) & "," & Chr(34) & sep & Chr(34) & ")"
End If
Next cell`
Upvotes: 1