+ Reply to Thread
Results 1 to 5 of 5

runtime error 1004 in VBA

  1. #1
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    runtime error 1004 in VBA

    I decided to pick up a little VBA programming, and started a practice course online. I encountered a problem, and I can't solve it. Here is the piece of code posted on the site that gives out the error.

    Range(ActiveCell.Offset(, -1), ActiveCell.Offset(, 0)).Interior.Color = RGB(255, 0, 0)

    The idea behind the code is that a range is selected (the current cell and 1 cell to the left of it, 2 cells total, I believe) and the background color is changed to red. I cannot make it work like this, but it is the same as posted on the site.

    I got around the error by rewriting it like this:

    ActiveCell.Offset(, -1).Interior.Color = RGB(255, 0, 0)
    ActiveCell.Interior.Color = RGB(255, 0, 0)

    As a beginner I was just curious, what exactly is off with that 1st piece of code? Any ideas?

    P.S.

    I work with Excel 2010

  2. #2
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: runtime error 1004 in VBA

    Woops, I just noticed that there is a seperate place for VBA related questions. Can this thread be moved?

    Thread moved --6StringJazzer
    Last edited by 6StringJazzer; 09-24-2014 at 10:25 AM.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: runtime error 1004 in VBA

    Please Login or Register  to view this content.
    It works just fine ... provided the active (selected) cell is not in column A.

    In column A, you can't have ActiveCell.Offset(, -1), that is, a cell to the left of column A

    The ActiveCell.Offset(, 0) in ActiveCell.Offset(, 0) is redundant because the row parameter is omitted and defaults to 0 and the column parameter is 0 so the offset doesn't have an effect.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: runtime error 1004 in VBA

    What module is the code in, and what worksheet contains the active cell?

    The Range keyword is not qualified here. That is, it is not prefaced by what worksheet it refers to, like this

    Please Login or Register  to view this content.
    Without qualification in a Module (like Module1) Range refers to the active sheet.

    Without qualification in a Worksheet module (like Sheet1) Range refers to the sheet for that module.

    If your code is defaulting to one sheet but the ActiveCell is in another sheet, that code will cause an error.

    On preview, I see that TMS makes a good point. The code could be written more simply as

    Please Login or Register  to view this content.
    but that is not the source of the error you are getting.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: runtime error 1004 in VBA

    Thank you guys for your time!

    The problem was similar to what 6StringJazzer pointed out - I was trying to run the macro in Sheet 1, but in VBA the macro was written under Sheet 2, and that caused the error. As soon as I moved the macro from Sheet 2 to Sheet 1 it worked like a charm without any errors.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. runtime error 1004 vb
    By tamzgha in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-13-2013, 10:56 AM
  2. [SOLVED] VBA Error: Runtime Error 1004: AutoFilter method of Range class failed
    By jl22stac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 07:27 PM
  3. [SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004
    By charizzardd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2012, 03:34 PM
  4. [SOLVED] Runtime error 1004
    By Daniel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2005, 12:05 AM
  5. [SOLVED] Excel 2003 Macro Error - Runtime error 1004
    By Cow in forum Excel General
    Replies: 2
    Last Post: 06-07-2005, 09:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1