Run time error 9, out of range, error 438, error 1009

Question

My excel VBA code keeps giving me errors. I was running a lot of code perfectly fine a few days ago. Now I am working on new code, and it is not working!

some errors it gives me include

Run time error 9, out of range
error 438
error 1009

I think something is wrong, that does not necessarily have to do with the code

Sub GetTickers1()

    WS = Workbooks("Ticker Symbols for Rodman and Renshaw Conference 2015").Worksheets("Sheet1")

    Dim i As Integer

    For i = 2 To i = 254 Step 1



    WS.Cells(i, 2) = Left(Right("" + WS.Cells(i, 1), 5), 4)

    Next i

End Sub

Answer 1

This code was never working (at least not as it is currently written).

The very first line cannot execute without the Set keyword and will raise the 438 error.

If you rectify that error, then you can expect a 1009 error if either:

• The workbook specified on the right-side of that statement isn't open in the current active instance of Excel Application
• The worksheet specified on the right-side of that statement doesn't exist within the workbook specified.

For your 424 error, while you can sometimes rely on an object's default property while (e.g., Debug.Print Range("A1") will print the .Value), an assignment statement doesn't automatically assign to the default property, so you'll have to specify: WS.Cells(i, 2).Value = ... Also, I'm not sure what you were trying to do with the "" + but that could causing a type mismatch -- read up on whether it's preferable to use the + or & operator for concatenating strings -- both are allowable, but the + can get you in trouble sometimes, so revised as:

WS.Cells(i, 2).Value = Left(Right(WS.Cells(i, 1), 5), 4)

NB also that your For i = 2 To i = 254 Step 1 will not do anything. Try instead For i = 2 to 254. A step of 1 is always implied unless otherwise specified, so you don't need to explicitly do Step 1.

Answer 2

You need to use Set in the first line.