Excel VBA Object Defined Error

Question

I am getting an object defined error with the below code. Any idea what could i be doing wrong? Thanks

Sub Loop_Test2()

    Dim i As Integer
    Dim j As Integer
    Dim CountAll As Integer
    Dim CountXL As Integer

    ActiveSheet.Range("A1").Activate

    CountAll = ActiveSheet.Range("A35")
    MsgBox CountAll

    For j = 1 To CountAll
      i = 1

This is where the error occurs:

      CountXL = Cells(i, j).Value

Continued:

      MsgBox CountXL

      For i = 1 To CountXL + 2
        Cells(i + 2, j) = "Row " & i & " Col " & j
      Next i
    Next j

End Sub

I think it is an incorrect assignment. I'm not familiar with the correct syntax.

Error Details: "Run time error 1004. Application defined or object defined error

Hi. I modified the post after doing some test. Looks like the error is in the "CountXL" assignment. — Nadz, Jul 02 '15 at 05:32
I think it is because i am not initializing the "i". Any idea how to do it correctly? Also i think the way i am assigning the value to "CountXL" is not done correctly — Nadz, Jul 02 '15 at 05:34
just set a value to i like `i=1` . Meanwhile, please use long instead of integer to enhance your script performance. — keong kenshih, Jul 02 '15 at 05:39
It works fine thanks. Still getting an error when assigning Cells(i,j) to CountXL. "Type Mismatch" error. Did change to "Long" instead but i think the syntax might be incorrect? — Nadz, Jul 02 '15 at 05:41
What was the value in the Cells(i,j) ? does it belong to long type? — keong kenshih, Jul 02 '15 at 05:43
Cells(i,j) for example points to A2, which holds a number. This is to iterate through the rows. — Nadz, Jul 02 '15 at 05:44
Okay sorry, You are right :) I should do "i=2" to point to "A2" — Nadz, Jul 02 '15 at 05:46
@keongkenshih you should post it as an answer, and OP should accept it, if that solves the issue. — bonCodigo, Jul 02 '15 at 07:35

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

Before you edit your question, you was forget to initial your i .So just set the value for i.

In future, you may use Option Explicit at the top of Sub to make sure you declare the variable before using it.

So for your case, just need set i=1, and please declare all the variables as long instead of integer. You may refer here to find out the reason why use long instead of integer.

edited May 23 '17 at 12:06

Community

1
1

answered Jul 02 '15 at 08:45

keong kenshih

524
3
8

Excel VBA Object Defined Error

1 Answers1