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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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!
 
Upvote 0
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!
 
Upvote 0
Yup! This is a point that needs clarification. Wasted loads of time, before i figured this one out.
Thanks !
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top