Excel VBA application-defined or object-defined error

Question

I am having trouble resolving this issue regarding

application-defined or object-defined error

My codes as below

Dim intX as integer
Dim Uname As String
Dim Pword As String
Dim Epin As String

intX = 9
Uname = Cells(intX, 5)

Do
If Cells(intX, 4).Value > 0 Then
    Uname = Cells(intX, 5)
    Pword = Cells(intX, 6)
    Epin = Cells(intX, 7)
    Else
    GoTo Skips
    End If

'Bunch of codes

Skips:
    intX = intX + 1
Err_Clear:
    If Err <> 0 Then
    Err.Clear
    Resume Next
    End If

Loop Until IsEmpty(Uname)

My table basically has a column of login infos and a column of yes no being informed via a 1 = yes and 0 or null = no system, so if the cells(intX,4) has a 1, it will perform the bunch of codes

Uname or Cells(intX,5) has value until the end (or empty cell)

The code runs perfectly until the final loop (it finishes the task but return a application -defined or object-defined error hence the code won't run after the Loop Until IsEmpty(Uname)

The error points to

If Cells(intX, 4).Value > 0 Then

Any help please?

I suspect intX (curious name for a Long) is higher than the number of available rows. — Rory
What are the values of intX and of Cells(intX,4) when the error occurs? I suspect @Rory answer to be correct, as you do not seem to have any upper bound for checking intX. — Ron Rosenfeld
intX at the moment of error is 16, i did an adjustment and made intX back to integer, i made it Long because as it runs, it gives an overflow problem and points to intX = intX + 1 — Crays
1. Are you using Option Explicit? 2. Can you update the code by showing the variable declarations with their specific data type for the purpose their being used? — bonCodigo

Ralph Ralph · Accepted Answer · 2015-07-06T15:29:45

While I haven't been able to test this code, I sure hope that it is able to run without problems.

Dim intX As Integer
Dim Uname As String
Dim Pword As String
Dim Epin As String

For intX = 9 To 1000000
    If Trim(Sheet1.Cells(intX, 5).Value2) = vbNullString Then Exit Sub
    If IsNumeric(Sheet1.Cells(intX, 4).Value2) Then
        If Sheet1.Cells(intX, 4).Value2 > 0 Then
            Uname = Sheet1.Cells(intX, 5).Value2
            Pword = Sheet1.Cells(intX, 6).Value2
            Epin = Sheet1.Cells(intX, 7).Value2
        End If
    Else
        GoTo Skips
    End If

    'Bunch of codes

Skips:
Err_Clear:
    If Err <> 0 Then
        Err.Clear
        Resume Next
    End If

Next lngrow

Here are the things I changed: - explicit coding vs implicit coding using Sheet1. (or what ever sheet you are referring to up front to ensure that Excel is using the sheet you want it to - explicit coding again using .value2 at the end - before checking .Cells(intX, 4).value2 if it is above zero I added another check if it is actually numeric - exchanging the endless loop for a definite one - replacing IsEmpty for a comparison against vbNullString as IsEmpty per help file should be only used to check if a variable has beeen inizialized. But a cell isn't really a variable.

Hope this helped.

Excel VBA application-defined or object-defined error

1 Answers