Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
I've been running some code successfully for a few weeks now, the only thing
that I've changed is that I now copy a range of data from the Main Sheet to a Working Sheet before I start manipulating the data. I haven't change the actual code below that now gives me the ERROR "Selection Method of Range class Failed" Set Rng = Worksheets(SheetName2).UsedRange.Rows 'I Get the Error trying to execute the next line Application.Worksheets(SheetName2).Range("A" & First_Row, "G" & Rng.Rows.Count).Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
Is the sheet visible at the time you go to make the selection?
Jim Thomlinson "HotRod" wrote: I've been running some code successfully for a few weeks now, the only thing that I've changed is that I now copy a range of data from the Main Sheet to a Working Sheet before I start manipulating the data. I haven't change the actual code below that now gives me the ERROR "Selection Method of Range class Failed" Set Rng = Worksheets(SheetName2).UsedRange.Rows 'I Get the Error trying to execute the next line Application.Worksheets(SheetName2).Range("A" & First_Row, "G" & Rng.Rows.Count).Select |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
Yes this solved the problem I didn't realize I couldn't select cells if the
page didn't have the focus. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
That is why I always try to avoid selecting. It requires a lot more code and
a lot more effort. I am not big on effort. Jim Thomlinson "HotRod" wrote: Yes this solved the problem I didn't realize I couldn't select cells if the page didn't have the focus. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
Jim
I guess that's why we use code to begin with, none of us are big on effort. I'm currently developing a system that matches peoples names to determine if they are the same name but spelt different. In a test of 23000+ names it now runs at a 99.8% accaracy. splitting the number of "UNIQUE' visitors to our centres into each quarter. Trust me this will save me a ton of work, of making sure that Mike Smith and Micheal Smith are the same person. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
From watching your posts you are getting mighty good at this. For you the
biggest step forward that you could make at this point would be to make greater use of worksheet, borkbook and range objects. Also name your sheets in code and refer to them by their code name. For example use Sheet1.select instead of Sheets.("Sheet1").select Jim Thomlinson "HotRod" wrote: Jim I guess that's why we use code to begin with, none of us are big on effort. I'm currently developing a system that matches peoples names to determine if they are the same name but spelt different. In a test of 23000+ names it now runs at a 99.8% accaracy. splitting the number of "UNIQUE' visitors to our centres into each quarter. Trust me this will save me a ton of work, of making sure that Mike Smith and Micheal Smith are the same person. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
Jim
Just a question since this really has been what I'm wondering 1) If I use Sheet1 instead of Worksheet("SheetName") are all of the sheets labeled sequentialy from Sheet1 - Sheet100, and what happens if someone changes the order of the worksheets? 2) I've now replaced the code Application.Worksheet("SheetName").Range() etc. with Rng.Range() etc. In the code below would it make more sense to create to Range Objects? 'COPY MAINSHEET TO WORKSHEET -------------------------------------------------------------------------------------------------- 'Select Range on first Sheet and then copy Values only. Set Rng = Worksheets(SheetName).UsedRange.Rows 'Clear old sheet first Application.Worksheets(SheetName2).Range("A" & WorkSheet_First_Row, "Z55500").Value = "" 'COPY DATA Application.Worksheets(SheetName2).Range("A2", "G" & (Rng.Rows.Count - (First_Row - WorkSheet_First_Row))).Value = _ Application.Worksheets(SheetName).Range("A" & First_Row, "G" & Rng.Rows.Count).Value |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
THANKS For all of your help.
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
What would you recommend instead of doing the following?
SheetName2 = "WorkSheet" 'Set Focus to WorkSheet Application.Worksheets(SheetName2).Select |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
What did I do? (Select Method of Range Class Failed )
Hi Rod
The best approach, in my opinion, when referencing a worksheet object is to use the object name. This is Sheet1, Sheet2 etc for sheets and is created when the sheet is added. You can change these in the VB editor in the properties window. These names can only be changed here and it doesn't matter what the sheet name is. For example The first worksheet created will have the object name Sheet1 regardless of where it is in the actual order. It may have a worksheet name (shown on the tab) of Data. I usually rename (using the name property of the object) this sheet wsData and then reference the object directly. e.g. wsData Range("a1").copy Hope this helps. Nick "HotRod" wrote in message ... Jim Just a question since this really has been what I'm wondering 1) If I use Sheet1 instead of Worksheet("SheetName") are all of the sheets labeled sequentialy from Sheet1 - Sheet100, and what happens if someone changes the order of the worksheets? 2) I've now replaced the code Application.Worksheet("SheetName").Range() etc. with Rng.Range() etc. In the code below would it make more sense to create to Range Objects? 'COPY MAINSHEET TO WORKSHEET -------------------------------------------------------------------------------------------------- 'Select Range on first Sheet and then copy Values only. Set Rng = Worksheets(SheetName).UsedRange.Rows 'Clear old sheet first Application.Worksheets(SheetName2).Range("A" & WorkSheet_First_Row, "Z55500").Value = "" 'COPY DATA Application.Worksheets(SheetName2).Range("A2", "G" & (Rng.Rows.Count - (First_Row - WorkSheet_First_Row))).Value = _ Application.Worksheets(SheetName).Range("A" & First_Row, "G" & Rng.Rows.Count).Value |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select method of Range class failed - but why??? | Excel Programming | |||
select method of range class failed | Excel Programming | |||
select method of range class failed | Excel Programming | |||
select method of range class failed | Excel Programming | |||
error 1004 Select method of Range class failed | Excel Programming |