CODEWITHSUNDEEP
vbaexcelinitialization
Brandan B
Brandan B

Reputation: 474

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

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

Upvotes: 0

Views: 841

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 0

user3167003
user3167003

Reputation:

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

Upvotes: 2

Related Questions