Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Wrexham, Clwyd, Wales
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    runtime error 1004 (Excel 97)

    Runtime error 1004 pops up most (but not every) time I run a macro. Message is Method 'Cells' of Object '_Global' failed

    Offending code is :-

    ' First find the next free column in the Annual Total Sheet
    Worksheets("Annual").Activate

    i = 27 ' Set last row
    j = 2 ' Start column

    Do While Worksheets("Annual").Cells(i, j).Value <> "" ' Run across the row while there are numbers in the cell
    j = j + 1 ' increment the column
    Loop

    ' Now i=27 and j contains the number of the last filled column in the "Annual" worksheet



    ' Copy the week we're losing
    Worksheets("Stock Valuation").Activate
    Range("C1:C27").Select
    Selection.Copy

    ' And save it into the next free column of "Annual"
    Worksheets("Annual").Activate
    Range(Cells(1, j), Cells(27, j)).Select
    ActiveSheet.Paste Destination:=Worksheets("Annual").Range(Cells(1, j), Cells(27, j))


    And the error crops up at Range(Cells(1,j), Cells(27,j).select. (last-but one line, although the VB carries on a bit from there)

    Aagghhhhhhh

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: runtime error 1004 (Excel 97)

    I think the entire piece of code you posted can be replaced by this:
    <pre>j = Worksheets("Annual").Range("IV27").End(xlToLeft).C olumn + 1
    Worksheets("Stock Valuation").Range("C1:C27").Copy _
    Destination:=Worksheets("Annual").Cells(1, j)
    </pre>

    This avoids selecting ranges, and avoids confusion about what Cells refers to.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Wrexham, Clwyd, Wales
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: runtime error 1004 (Excel 97)

    Thanks, Hans

    Your code appears to work fine : I have very occasional need to concoct something in VBA and when I do, I always seem to go about it a long and complex way when there's (usually) a much easier route. One day, perhaps, I'll learn something about what I'm doing, but don't hold your breath - at my advanced years, new stuff doesn't come easy !! I'll take a more detailed squint at your recommendation and see what I can learn from it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: runtime error 1004 (Excel 97)

    If you have questions about the code, don't hesitate to post them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •