Page 1 of 2 12 LastLast
Results 1 to 10 of 15
Like Tree1Likes

Method Of Range Class Failed (SOLVED)

This is a discussion on Method Of Range Class Failed (SOLVED) within the Excel Questions forums, part of the Question Forums category; i just figured out that when you change sheets too often in VBA and get an "Activate Method Of Range ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Location
    NSW, Australia
    Posts
    110

    Default



    i just figured out that when you change sheets too often in VBA and get an "Activate Method Of Range Class Failed" or a "Select Method Of Range Class Failed" etc, simply retype the sheets command before it

    for example :

    Sheets("Work1").Select
    Range("A1:D50").Select

    OR

    Sheets(Work1").Range("A1:D50").Select

    this code in VBA, if used too often or the mentioned sheet is not the currently selected sheet in a Sub, will cause errors, and to correct this, simply type

    Sheets("Work1").Select
    Sheets("Work1").Range("A1:D50").Select

    this makes sure that the sheet is selected before running any other range/sheet type commands.

    I hope this clears up some other ppl's problems, it had me for a few weeks.
    Levi22 likes this.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Elemental, the next best thing to do is to not select anything at all ! that prevents that error further more.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Dec 2007
    Location
    SC
    Posts
    6

    Default Method Of Range Class Failed (SOLVED)

    I was having a problem with this and was about to start a new thread, but I found this solution, and it fixed my problem! I'll go ahead and explain my problem though so that other people having the same problem will know this solution works.

    I'm using Excel 2003 and Windows XP.

    I created a macro that selected a range of cells on a worksheet, unlocked them, copied them, switched to another worksheet, pasted the values there, and re-locked the original cells.

    When I ran the macro, it worked fine. However, when I tried to paste the macro code into the code for a control button, I kept getting the "Method Of Range Class Failed" error, which is a run-time error 1004.

    I followed Elemental's suggestion and put the 'Sheets("sheetname").' code right before the 'Range('A1:D50").Select' code, and now it works perfectly!

    This message board is a great resource. Thanks for keeping it up so well!

  4. #4
    New Member
    Join Date
    Mar 2011
    Posts
    1

    Default Re: Method Of Range Class Failed (SOLVED)

    I am using Excel 2010 and was having similar issues assigning a macro to a check box. After nearly an hour of trying to debug your post saved me! Countless thank yous!

  5. #5
    New Member
    Join Date
    Jun 2011
    Posts
    1

    Default Re: Method Of Range Class Failed (SOLVED)

    Yup! This is a point that needs clarification. Wasted loads of time, before i figured this one out.
    Thanks !

  6. #6
    New Member
    Join Date
    Jan 2013
    Posts
    11

    Default Re: Method Of Range Class Failed (SOLVED)

    I've tried using the solution suggested here however I still get the same "Select method of range class failed"

    Does anyone have any ideas on why else this error occurs and how it can be fixed?

    Thanks very much!

  7. #7
    shg
    shg is online now
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,413

    Default Re: Method Of Range Class Failed (SOLVED)

    Post the code that fails.
    Microsoft MVP - Excel

  8. #8
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Method Of Range Class Failed (SOLVED)

    Code is here:

    Hiding rows macro not working

    AmezNez it would be best to stick to the one thread so anyone looking can see what has been suggested (and has/hasnt worked!)
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  9. #9
    New Member
    Join Date
    Mar 2013
    Posts
    1

    Default Re: Method Of Range Class Failed (SOLVED)

    Since I seem to be getting the same error "Method of Range Class Failed" when i run the following code, I thought I would post on here. I'm using Windows 7 and Excel 2010. I'm making a ad hoc database of info and I would like a color an area of cells under certain situations by hitting a button on the main portion of the sheet, but I keep getting the 1004 "Method of Range Class Failed" when I hit the button. The xx is an incremental value that I use to keep the position of the cells in the right area.

    Code:
    Sub Backup()
    Dim xx As Integer
    
    xx = Sheets("Data").Range("C1").Value
    Sheets("Data").Range("B3:P4").Offset(xx, 0).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.25
    .PatternTintAndShade = 0
    End With
    End Sub

  10. #10
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Method Of Range Class Failed (SOLVED)

    Hi
    Avoid using Select at all:

    Code:
    Sub Backup()
    Dim xx As Integer
    xx = Sheets("Data").Range("C1").Value
    With Sheets("Data").Range("B3:P4").Offset(xx, 0).Interior
            
      .Pattern = xlSolid
            
      .PatternColorIndex = xlAutomatic
            
      .ThemeColor = xlThemeColorDark1
            
      .TintAndShade = -0.25
            
      .PatternTintAndShade = 0
        
    End With
    End Sub
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com