"Run-time error '424': Object required" in Excel VBA

Question

I want to create an enquiry sheet for users to check their own data. When user inputting some information which fits to the data list, the rest of their data will show up.

I've type the code as below and I guess probably it's lacking something because the error code "Run-time error '424': Object required" keeps popping up. And I have no idea what's lacking. Here's the code:

Sub EnquiryChecking()
    Dim i As Integer

    If (Sheet1.Cells(3, 3) = DataList.Cells(i, 1)) And (Sheet1.Cells(4, 3) = DataList.Cells(i, 4)) And (Sheet1.Cells(5, 3) = DataList.Cells(i, 5)) Then
        Sheet1.Cells(7, 3) = DataList.Cells(i, 2)
        Sheet1.Cells(8, 3) = DataList.Cells(i, 3)
        Sheet1.Cells(9, 3) = DataList.Cells(i, 6)
        Sheet1.Cells(10, 3) = DataList.Cells(i, 7)
        Sheet1.Cells(11, 3) = DataList.Cells(i, 8)
        Sheet1.Cells(12, 3) = DataList.Cells(i, 9)
    Else
        Sheet1.Cells(14, 2) = "Error Input"
    End If
End Sub

Many thanks!

A further update:
I added a for loop for i and now the code can run. But now the Sheet1.Cell doesn't show the DataList.Cell data (even the input is not correct, it doesn't show "Error Input". What happened? Also, what should I add if I want to show the data instantly in the cell?
Here's the code:

Sub EnquiryChecking()    
    Dim i As Integer
    For i = 1 To i
        If (ActiveWorkbook.Sheets("Sheet1").Cells(3, 3) = ActiveWorkbook.Sheets("DataList").Cells(i, 1)) And (ActiveWorkbook.Sheets("Sheet1").Cells(4, 3) = ActiveWorkbook.Sheets("DataList").Cells(i, 4)) And (ActiveWorkbook.Sheets("Sheet1").Cells(5, 3) = ActiveWorkbook.Sheets("DataList").Cells(i, 5)) Then
            Worksheets("Sheet1").Cells(7, 3) = Worksheets("DataList").Cells(i, 2)
            Worksheets("Sheet1").Cells(8, 3) = Worksheets("DataList").Cells(i, 3)
            Worksheets("Sheet1").Cells(9, 3) = Worksheets("DataList").Cells(i, 6)
            Worksheets("Sheet1").Cells(10, 3) = Worksheets("DataList").Cells(i, 7)
            Worksheets("Sheet1").Cells(11, 3) = Worksheets("DataList").Cells(i, 8)
            Worksheets("Sheet1").Cells(12, 3) = Worksheets("DataList").Cells(i, 9)
            Exit For
        Else
            Sheet1.Cells(14, 2) = "Error Input"
        End If
    Next i
End Sub

No. Dim tells what kind of variable i will be. Somewhere you need to declare the value, otherwise i could be 0 or nothing, which will not make sense. You can either pass the value or declare i=1 or for i=1 to 10, etc. — Darrell H, Jan 04 '19 at 04:06
@DarrellH: You mean "initialise" or "assign" instead of "declare" the variable. Dragonfly is correct - the variable is declared. However, as you note, it is never assigned a value, so the default value is "0", which is invalid in `Cells`. — AJD, Jan 04 '19 at 04:30
@AJD you are right. I added the for loop and there's no error now. But I now don't understand why the cells don't show the data from Data.List...? — dragonfly, Jan 04 '19 at 05:13
`For i = 1 to i` is just plain wrong. You need `For i = 1 to __` to make this work. Only you know what that __ is. — AJD, Jan 04 '19 at 19:35

score 0 · Answer 1 · answered Jan 04 '19 at 03:42

0

Replace DataList by ActiveWorkbook.Sheets("DataList") to avoid getting that error.

Refer THIS for more information.

answered Jan 04 '19 at 03:42

Gurmanjot Singh

10,224
2
19
43

Thanks. But but I got run-time error '1004' this time...? – dragonfly Jan 04 '19 at 04:06
Many thanks! I modified to Worksheets("DataList") to point as worksheet and it works fine. – dragonfly Jan 04 '19 at 05:10
Arguably, addressing the sheets by their [code name](https://stackoverflow.com/a/41481428/11683) (which `DataList` would be) is better than using the user-specified name which can be easily changed by users. It's just that you need to put `DataList` in the sheet's "Code name" property first which the OP apparently didn't do. – GSerg Aug 17 '19 at 14:12

"Run-time error '424': Object required" in Excel VBA

1 Answers1