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. — user4039065
– user4039065, Commented May 19, 2015 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
– Brandan B, Commented May 19, 2015 at 17:42

user3167003 · Accepted Answer · 2015-05-19 17:43:14Z

2

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

answered May 19, 2015 at 17:43

user3167003

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

Comments

Community · Accepted Answer · 2017-05-23 11:50:43Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related