Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

VBA runtime 1004 error

  1. Aug 5, 2010 #1
    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?
  2. jcsd
  3. Aug 5, 2010 #2


    User Avatar
    Homework Helper

    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.
    Last edited: Aug 5, 2010
  4. Aug 5, 2010 #3
    Thanks. It is working now.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook