Method Of Range Class Failed (SOLVED)

Elemental

Board Regular
Joined
Jul 13, 2002
Messages
110
:p

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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

daisydogflutist

New Member
Joined
Dec 3, 2007
Messages
6
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!
 

Supersinkingship

New Member
Joined
Mar 12, 2011
Messages
1
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!
 

Quarky

New Member
Joined
Jun 29, 2011
Messages
1

ADVERTISEMENT

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

AmezNez

New Member
Joined
Jan 15, 2013
Messages
11
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!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,774
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Post the code that fails.
 

LoganG

New Member
Joined
Mar 21, 2013
Messages
1
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
[INDENT]xx = Sheets("Data").Range("C1").Value

[/INDENT]
Sheets("Data").Range("B3:P4").Offset(xx, 0).Select
    [INDENT]With Selection.Interior[/INDENT]
        [INDENT=2].Pattern = xlSolid[/INDENT]
        [INDENT=2].PatternColorIndex = xlAutomatic[/INDENT]
        [INDENT=2].ThemeColor = xlThemeColorDark1[/INDENT]
        [INDENT=2].TintAndShade = -0.25[/INDENT]
        [INDENT=2].PatternTintAndShade = 0[/INDENT]
    [INDENT]End With[/INDENT]
End Sub
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,320
Latest member
sixnine0312
Top