Need some advice on how to tweak my visual basic code so it doesn't error out when trying to fi-CodePudding

I finally got some code working to fill in the blank cells in a column of data. It works perfectly the first time I run it. However, after having filled in all those blank cells, it apparently is still looking for some blank cells to work on, but there aren't any, so it's throwing an error: Run-time error '1004' "No cells were found".

How could I change this code so that it only fills down blank cells if there are blank cells present in the data?

Sub FillRow()

S_Data.Activate
LR = S_Data.Cells(Rows.Count, "C").End(xlUp).Row

S_Data.Range("H2:H" & LR).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"


End Sub

I have tried different scripts I found online, including For loops, if then loops, but I'm not well versed in VB and wasn't able to get those working.

CodePudding user response：

You need to trap or ignore the error when there are no blanks in the range:

Sub FillRow()
    Dim rng As Range, LR As Long
    
    LR = S_Data.Cells(S_Data.Rows.Count, "C").End(xlUp).Row
    On Error Resume Next 'ignore error if no blanks
    Set rng = S_Data.Range("H2:H" & LR).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0      'stop ignoring errors
    
    'any blank cells?
    If Not rng Is Nothing Then
        rng.FormulaR1C1 = "=R[-1]C"
    End If
End Sub