Fix VBA Runtime 1004 Error in Excel Program

[dmatador]

This is my first attempt at writing a VBA program. I am just trying to square items in excel in column 'A' and output the answer to column 'B'.

Option Explicit

Sub timestimes()
Dim n As Integer
Dim i As Integer
Dim z As Integer

n = Range("A").Count

For i = 1 To n
z = Range("A").Cells(i).Value
Range("B").Cells(i).Value = z * z * z
Next i
End Sub

It's giving me the previously noted runtime error along with the message Method 'Range' of object '_global' failed. I am clueless as to how to fix this problem since I know very very little about the language and working with excel like this. Any suggestions?

 

[hotvette]

I see two problems:

1) "A" isn't a range. You need something like: Range("A2:G85")

2) You can't count a range. You count cells within a range. Example: n = Range("A1:B33").Cells.Count

You can use n = Columns("A").Cells.Count but you'll get an overflow error if you are using Excel 2007 because an integer can't hold that big a number (1048576). You'd need to declare n as a Long.

 

[dmatador]

Thanks. It is working now.