Excel Visual Basic Run-time error '1004' when initializing a variable

Question

I am learning visual basic and this script I'm using is coming up with an error when I am initializing the variable i.

I'm not sure what the problem is but I am getting the error message:

Run-time error '1004': Application-defined or object-defined error

Here is my code:

Sub excelmacro()

Sheets("Sheet1").Select
Range("A1").Select

Sheets("Sheet2").Select
Range("B1").Select

i = 1

While i <> 10
    If Len(ActiveCell.Value) > 1 Then
        Sheets("Sheet1").Select
        xname = Right(ActiveCell.Value, Len(ActiveCell.Value) - 6)
        xsalary = Right(ActiveCell.Value, Len(ActiveCell.Offset(2, 0).Value) - 8)
        xdesignation = Right(ActiveCell.Value, Len(ActiveCell.Offset(1, 0).Value) - 13)

        Sheets("Sheet2").Select
        ActiveCell.Value = xname
        ActiveCell.Offset(0, 1).Value = xdesig
        ActiveCell.Offset(0, 3).Value = xsalary

        ActiveCell.Offset(1, 0).Select
        Sheets("Sheet1").Select
        ActiveCell.Offset(3, 0).Select
    Else
        i = 10
    End If
Wend

End Sub

If your debug is pointing to the `i = 1` line then the error should be **Compile error: variable not defined**. This occurs when you specify `Option Explicit` at the top of the module sheet but do not declare your variables (e.g. **Dim i as long**) before using them. — , May 19 '15 at 17:38
Also when I put Dim i As Long before I set a value for 1 I get the same error for that line — Brandan B, May 19 '15 at 17:42

score 2 · Accepted Answer · answered May 19 '15 at 17:43

you do not need the variable i in your code anyway! just kick the line initializing i out.

The if statement in your loop that uses i is basically to escape the loop and can be shortened to:

While  Len(ActiveCell.Value) > 1 
    Sheets("Sheet1").Select
    xname = Right(ActiveCell.Value, Len(ActiveCell.Value) - 6)
    xsalary = Right(ActiveCell.Value, Len(ActiveCell.Offset(2, 0).Value) - 8)
    xdesignation = Right(ActiveCell.Value, Len(ActiveCell.Offset(1, 0).Value) - 13)

    Sheets("Sheet2").Select
    ActiveCell.Value = xname
    ActiveCell.Offset(0, 1).Value = xdesig
    ActiveCell.Offset(0, 3).Value = xsalary

    ActiveCell.Offset(1, 0).Select
    Sheets("Sheet1").Select
    ActiveCell.Offset(3, 0).Select

Wend

score 0 · Answer 2 · edited May 23 '17 at 11:50

This may be a good time to start practising the methods detailed in How to avoid using Select in Excel VBA macros.

Your code is repeatedly retrieving different lengths of the right-most characters from ActiveCell but using the length of the values from rows below the active cell to determine how many characters to retrieve. It seems that you should be retrieving the characters from the same cell that you are using to determine the length.

Sub excelmacro()
    Dim i As Long, xname As String, xsalary As String, xdesignation As String

    With Sheets("Sheet1")
        For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row Step 3
            If CBool(Len(.Cells(i, "A").Value)) Then
                xname = Right(.Cells(i, "A").Value, Len(.Cells(i, "A").Value) - 6)
                xdesignation = Right(.Cells(i + 1, "A").Value, Len(.Cells(i + 1, "A").Value) - 13)
                xsalary = Right(.Cells(i + 2, "A").Value, Len(.Cells(i + 2, "A").Value) - 8)

                With Sheets("Sheet2")
                    .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = xname
                    .Cells(Rows.Count, "B").End(xlUp).Offset(0, 1) = xdesignation
                    .Cells(Rows.Count, "B").End(xlUp).Offset(0, 3) = xsalary
                End With

            End If
        Next i
    End With
End Sub

I've retained your use of a string variable for the salary although you may be better served by using a variable of type double and converting the text with a CDbl() wrapper. The second use of xdesig instead of xdesignation was corrrected.

Excel Visual Basic Run-time error '1004' when initializing a variable

2 Answers2