Jannik732
Jannik732

Reputation: 41

Conditionally Edit Excel function in VBA

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

Answers (1)

Naresh
Naresh

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

Related Questions