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
– Plagon, Commented Jul 12, 2017 at 7:22
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
– Plagon, Commented Jul 12, 2017 at 7:35
@UGP, yes , in the same line . And when i remove the condition (С.Offset(0, -5).Value = 1) all works fine — maxim465
– maxim465, Commented Jul 12, 2017 at 7:41

Vityata · Accepted Answer · 2017-07-12 09:16:22Z

1

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...

answered Jul 12, 2017 at 9:16

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Offset (Runtime error 424 object required)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related