Method Of Range Class Failed (SOLVED)

[Elemental]

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.

 

[Juan Pablo González]

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

 

[daisydogflutist]

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]

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]

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

 

[AmezNez]

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]

Post the code that fails.

 

[Firefly2012]

Code is here:

http://www.mrexcel.com/forum/excel-questions/691332-hiding-rows-macro-not-working.html

AmezNez it would be best to stick to the one thread so anyone looking can see what has been suggested (and has/hasnt worked!)

 

[LoganG]

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.

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]

Hi
Avoid using Select at all:

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