CODEWITHSUNDEEP
excelvbastringexcel-formula
user16812393
user16812393

Reputation:

String in a formula in excel VBA

I'm struggling to a string in iferror function in the following code. i want string to be variable and a input box pops up for the string specifying what the user wants if an error pops up.

Sub test_IFERROR()
Dim frange As Range, xcell As Range
Dim xAddress As String
Dim xStr As String
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set frange = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set frange = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not frange Is Nothing Then
    xStr = Application.InputBox("Replace blank cells with what?", "Kutools for Excel")
        xUpdate = Application.ScreenUpdating
        Application.ScreenUpdating = False
        For Each xcell In frange
            c.Formula = "=IFERROR(" & Right(c.Formula, Len(c.Formula) - 1) & ","" & xStr & "")"
        Next xcell
    End If
    Set frange = Nothing
End Sub

Upvotes: 1

Views: 569

Answers (1)

Cristian Buse
Cristian Buse

Reputation: 4608

There are several problems with your code and all of them are because you are not using Option Explicit. If you did, you would immediately see that you are using the incorrect variables.

The ws variable is never declared or assigned. So you should change:

Set frange = ws.Cells.SpecialCells(xlCellTypeFormulas)

to:

Set frange = frange.SpecialCells(xlCellTypeFormulas)

which also is not a good idea because if there are no formulas the frange remains as the original selection (error is ignored). So, it's better (and easier to read) if you have 2 separate ranges. One for the initial selection and one for the formulas.

xUpdate is also never declared and moreover you never use it to restore the state of the Application.ScreenUpdating back to what it was.

There is no c variable. You loop iterator is called xcell so using c.Formula makes no sense unless you actually intended xcell.Formula.

Finally you should do what @Rory suggested (part of it) and replace ","" & xStr & "")" with ",""" & xStr & """)" so that the actual value of xStr is concatenated.

Also, you might want to restrict the number of times you can apply IFERROR to the same cell because if you run your procedure a couple of times on the same selection you will end up with something like =IFERROR(IFERROR(IFERROR(... which might not be what you want.

I would also rename the variables to more meaningful names. For example I would rename xAddress to currentSelectionAddress.

All of the above can be summed up in:

Option Explicit

Sub test_IFERROR()
    Dim selectedRange As Range
    Dim formulaRange As Range
    Dim cell As Range
    Dim currentSelectionAddress As String
    Dim blankReplacement As String
    Dim appScreenUpdate As Boolean
    
    On Error Resume Next
    currentSelectionAddress = Application.ActiveWindow.RangeSelection.Address
    Set selectedRange = Application.InputBox("Please select a range", "Kutools for Excel", currentSelectionAddress, Type:=8)
    Set formulaRange = selectedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If formulaRange Is Nothing Then Exit Sub
    
    blankReplacement = Application.InputBox("Replace blank cells with what?", "Kutools for Excel")
    appScreenUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each cell In formulaRange
        cell.Formula = "=IFERROR(" & Right(cell.Formula, Len(cell.Formula) - 1) & ",""" & blankReplacement & """)"
    Next cell
    Application.ScreenUpdating = appScreenUpdate
End Sub

I do not understand why you're asking the user to replace blank cells because your code does no check to see if the formulas return a blank value. I was thinking that you are replacing errors rather than blanks so maybe you should update "Replace blank cells with what?" with "Replace error cells with what?".

Upvotes: 1

Related Questions