Excel VBA Runtime error 1004 trying to assign range-CodePudding

I am trying to filter out empty cells across a specified range and found this code. Testing results in a runtime error 1004 no cells found. I looked through the locals window and Config Range doesnt seem to be assigning properly as a range when I look into the locals window. I dont typically use VBA so I am not sure if there is some aspect of it I am missing

        Set ConfigRange = Worksheets("Sheet1").Range("L72:L92")
        For Each cell In ConfigRange.SpecialCells(xlCellTypeFormulas)
           MsgBox (cell) 'test msg
        Next cell

Thanks

CodePudding user response：

Thanks FaneDuru I missed reviewing the code I was using from another project. They wanted to filter and leave in cells with formulas while I just wanted to filter out empty cells. Changed SpecialCells to use xlCellTypeConstants.

    Set ConfigRange = Worksheets("Sheet1").Range("L72:L92")
    For Each cell In ConfigRange.SpecialCells(xlCellTypeConstants)
       MsgBox (cell) 'test
    Next cell

CodePudding user response：

that range simply had no formulas

you need to check for it first, like in the following code:

Sub test()

    Dim configRange As Range
        Set configRange = Worksheets("Sheet1").Range("L72:L92")
    
    Dim configFormulaRange As Range
        If GetSpecialCells(configRange, xlCellTypeFormulas, configFormulaRange) Then
        
            Dim cell As Range
            For Each cell In configRange.SpecialCells(xlCellTypeFormulas)
               MsgBox (cell) 'test msg
            Next cell
        End If
        
End Sub

Function GetSpecialCells(rng As Range, cellType As XlCellType, retRng As Range) As Boolean
    Set retRng = Nothing
    On Error Resume Next
    Set retRng = rng.SpecialCells(cellType)
    GetSpecialCells = Not retRng Is Nothing
End Function