Filling empty cells - VBA-CodePudding

I'm trying to fill empty cells in a specified range. I have used code taken from other questions to write this:

Dim rCell As Range

For Each rCell In ws.Range("E36:G45")
    If IsEmpty(rCell.Value) = True Or IsNull(rCell.Value) = True Or rCell.Value = "" Then
    rCell.Value = -999
    End If
Next rCell

This doesn't work, neither does the following (where the Boolean is removed):

For Each rCell In ws.Range("E36:G45")
    If IsEmpty(rCell.Value) Or IsNull(rCell.Value) Or rCell.Value = "" Then
    rCell.Value = -999
    End If
Next rCell

The range E36:G45 contains a mix of empty and non-empty cells.

I have tried clearing the contents.

There are no sub procedures that could make the cells un-editable.

CodePudding user response：

Try simplifying your criteria; you can use len() combined with trim() to help with this, which will give your results as either 0 or >0:

dim cellRef as range
for each cellRef in ws.range("E36:G45")
    Select case len(trim(cellRef.value))
        Case >0
        Case else
            cellRef.value = -999
    End select
Next cellRef

CodePudding user response：

My guess is that the code is looking at a different worksheet - I suspect it's writing those -999s elsewhere.

If I were doing this, I'd use the following code:

Sub Fill_empty_cells()
On Error Resume Next
ws.Range("E36:G45").SpecialCells(xlCellTypeBlanks).Value = -999
On Error GoTo 0
End Sub

This definitely fills in cells that have been cleared using ClearContents.

That said, your original code does that also, hence my theory it's not looking at the same sheet you are.

To fully debug what's going on though, why not create some commentary so you read what's going on:

Sub test()
    Dim rCell As Range
    Set ws = ActiveSheet
    For Each rCell In ws.Range("E36:G45")
        If IsEmpty(rCell.Value) = True Or IsNull(rCell.Value) = True Or rCell.Value = "" Then
            rCell.Value = -999
        Else
            Debug.Print "Not filling cell " & rCell.Address & " of sheet [" & rCell.Parent.Name & "] because it contains """ & rCell.Value & """"
        End If
    Next rCell
End Sub

CodePudding user response：

Sorry to have wasted everyone's time but I think I might have found an answer...