Using Offset (Runtime error 424 object required)

Question

I want the program to look for “1” in column “U34:U99” .And on the order number of “1” checked the value in certain sheets . For example : First “1” (Лист1),Second “1” (Лист2) … But the error jumps out (Runtime error 424 object required) in line : If С.Offset(0, -5).Value = 1 And C.Value = 1 Then

    Sub обща()
    'для ситуации

    Dim k As Long, n As Long
    Dim C As Range
    Dim Diapozon As Range
    Set Diapozon = Range("U34:U99")
    k = 0
    n = 0
    For Each C In Diapozon.Rows

    If С.Offset(0, -5).Value = 1 And C.Value = 1 Then
    k = k + 1
    If ThisWorkbook.Sheets("Лист" & k & "").Range("R100").Value = 1 Then
    n = n + 1
    End If
    End If

    Next C
    MsgBox n

    End Sub

Instead of For Each C In Diapozon.Rows try For Each C In Diapozon — Plagon
Strange, even if i use .Rows what is not working, i dont get the error and without .Rows it just works fine. And you are sure that the error is still in the same line? — Plagon
@UGP, yes , in the same line . And when i remove the condition (С.Offset(0, -5).Value = 1) all works fine — maxim465

Vityata Vityata · Accepted Answer · 2017-07-12T09:16:22

This is something that works:

Option Explicit

Sub TestMe()

    Dim k           As Long
    Dim n           As Long
    Dim C           As Range
    Dim Diapozon    As Range

    Set Diapozon = Range("A1:A10")

    k = 0
    n = 0

    For Each C In Diapozon.Rows

        If C.Offset(0, 5).Value = 1 And C.Value = 1 Then
            k = k + 1
            If ThisWorkbook.Worksheets("Test" & k).Range("B10").Value = 1 Then
                n = n + 1
            End If
        End If

    Next C
    Debug.Print n

End Sub

I have changed the Ranges, the Worksheet name and the MsgBox to a debug.print. Possible errors are that you do not have a ListN or something else...

Using Offset (Runtime error 424 object required)

1 Answers