Looping Through Cells in Worksheet in Excel

Question

I am writing code in VBA for Excel to loop through all the cells in all the worksheets in a workbook and print certain cells based on their numeric value and position. However, I am unable to loop through the cells in a specific worksheet as I get a 424 object required error.

I have tried to debug my code and apparently the problems lies in my nested loop when I try to loop through the entire cells in a specific worksheet.

Public Sub loopThrougSheet()

Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'remember which worksheet is active in the beginning
Dim cell As Range
Dim data As Range

For Each ws In ThisWorkbook.Worksheets
ws.Activate
Set data = Intersect(ws.UsedRange, ws.Range("A2:AW1048576"))
    For Each cell In data
        If IsNumeric(cell.Value) And cell.Value > 0.5 Then
            Debug.Print (cell.Value)
        End If
    Next
Next

End Sub

The code does not compile as I get a 424 object required run time error.

Which line? Do you have an error values? I'd suggest first checking that cell is not an error and then doing your other checks (nested). — SJR
The error is on this line: For Each cell In data. The error I get is Run-time error: '424', Object required. — u_yousafzai54

Mikku Mikku · Accepted Answer · 2019-07-16T14:01:28

Try below:

Reason: Data was returning Nothing, sheet could be blank or there is nothing in the Intersection.

Added IsError Check for cell and if the Data is nothing.

Public Sub loopThrougSheet()

Dim ws As Worksheet
Dim starting_ws As Worksheet

Dim cel As Range
Dim data As Range

For Each ws In ThisWorkbook.Worksheets

    Set data = Intersect(ws.UsedRange, ws.Range("A2:AW1048576"))
        If Not data Is Nothing Then
        For Each cel In data
            If Not IsError(cel.Value) Then
            If IsNumeric(cel.Value) And cel.Value > 0.5 Then
                Debug.Print cel.Value
            End If
            End If
        Next
        End If
Next

End Sub

Looping Through Cells in Worksheet in Excel

1 Answers