Sheets("Results").Cells.Select ( Attention All MrExcel MVP's

[Elemental]

Sheets("Results").Cells.Select

is this code even valid ?

it says select method of Range CLass Failed,

even by typing

Sheets("Results").Select
Cells.Select

it fails on the cells.select

is there some problem with changing locations like that in VBA ?

thats what it outputs on a macro, but cant run in the VBA, and microsoft havnt done anything about it through all the versions of Excel, so what am i doing wrong by using the code it gives me from the macro recorder.

surely sombody has found a way around this, i could really use the help.

and it'd be great to know for other Excel users who may come across this error

thanks ppl.

 

[dk]

On 2002-08-22 15:29, Elemental wrote:
Sheets("Results").Cells.Select

is this code even valid ?

it says select method of Range CLass Failed,

even by typing

Sheets("Results").Select
Cells.Select

it fails on the cells.select

is there some problem with changing locations like that in VBA ?

thats what it outputs on a macro, but cant run in the VBA, and microsoft havnt done anything about it through all the versions of Excel, so what am i doing wrong by using the code it gives me from the macro recorder.

surely sombody has found a way around this, i could really use the help.

and it'd be great to know for other Excel users who may come across this error

thanks ppl.

Hello,

Which version of Excel are you using? I just tried this:-

Sub test()
Sheets("Results").Cells.Select
End Sub

and it worked fine. The active workbook must have a worksheet named 'Results'.

Off the top of my head I can't think what else it could be. Can you post your full code?

 

[Elemental]

Private Sub form_submit_Click()
Dim iRow As Integer, datesta As String, datefin As String
Dim Location
datesta = Sheets("Form").Range("C7")
datefin = Sheets("Form").Range("C11")
Location = Sheets("Form").Range("K15")
Sheets("Results").Cells.Select
Selection.Clear
Select Case Location
Case 1
Sheets("Data").Select
For iRow = 1 To 100
If ActiveSheet.Cells(iRow, "A").Value = datesta Then
ActiveSheet.Cells(iRow, "AD").Value = "Select Me"
Else
If ActiveSheet.Cells(iRow, "A").Value = datefin Then
ActiveSheet.Cells(iRow, "AD").Value = "Select Me"
End If
End If
Next iRow
Range("AD5").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy Sheets("Results").[A1]
Range("AD5").Select
Selection.End(xlDown).Select
ActiveCell.Value = ""
Selection.End(xlDown).Select
ActiveCell.Value = ""
Case 2
Sheets("Results").Select
Case 3
Sheets("Results").Select
Case 4
Sheets("Results").Select
Case 5
Sheets("Results").Select
Case 6
Sheets("Results").Select
Case 7
Sheets("Results").Select
Case Else
Sheets("Form").Select
End Select
End Sub

 

[Elemental]

can u make sense of this code ?

try pasting it in with fake values or somthing

but i know it runs via a macro, so what does VBA do that makes it more efficient by not running ?



silly thing

 

[dk]

OK,

I'm not entirely sure why Sheets("Results").Cells.Select doesn't work but you can do one of several things.

Activate the sheet first e.g.

Sheets("Results").Activate
Cells.Select

or use a far more preferable solution by not activating or selecting anything (much faster to do this).

Sheets("Results").Cells.Clear

This will allow you to get rid of the Selection.Clear line.

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-08-22 18:09

 

[Elemental]

yeah it fails on the sheets.cells.clear line also

i get a Clear Method Of Range Class Failed

if i try activate i get an Activate Method Of Range Class Failed

what's so hard about changing sheets in Excel for MS?

 

[Colo]

Hi Elemental,

When Sheets("Results") is the Activesheet, Sheets("Results").Cells.Select works fine.
But if it is not, error 1004 will be shown. Before select cells, we have to select or activate the parent sheet.

I amended your code a little...without useing select or activate method.(as Dan wrote)
Please try this one.

And can I ask you, are not you using protect method to results sheet?

HTH

<PRE>
<FONT color=red>Private <FONT color=red>Sub </FONT></FONT>form_submit_Click()

<FONT color=red>Dim </FONT>iRow <FONT color=red>As</FONT><FONT color=red> Integer</FONT>, datesta <FONT color=red>As</FONT><FONT color=red> String</FONT>, datefin <FONT color=red>As</FONT><FONT color=red> String</FONT>

<FONT color=red>Dim </FONT>Location, rng <FONT color=red>As</FONT> Range

datesta = Sheets("Form").[C7]

datefin = Sheets("Form").[C11]

Location = Sheets("Form").[K15]

Sheets("Results").Protect UserInterfaceOnly:=True <FONT color=#3366ff>'Just in case
</FONT>
Sheets("Results").Cells.Clear

Select <FONT color=red>Case </FONT>Location

<FONT color=red>Case </FONT>1

<FONT color=red>With </FONT>Sheets("Data")

<FONT color=red>For </FONT>iRow = 1 <FONT color=red>To </FONT>100

<FONT color=red>If </FONT>.Cells(iRow, "A").Value = datesta Then

.Cells(iRow, "AD").Value = "Select Me"

<FONT color=red>Else</FONT><FONT color=red>If </FONT>.Cells(iRow, "A").Value = datefin Then

.Cells(iRow, "AD").Value = "Select Me"

<FONT color=red>End If</FONT>

<FONT color=red>Next </FONT>iRow

<FONT color=red>Set </FONT>rng = .[AD5].End(xlDown)

<FONT color=red>Set </FONT>rng = .Range(rng, rng.End(xlDown))

<FONT color=red>Set </FONT>rng = .Range(rng, rng.End(xlToLeft))

<FONT color=red>Set </FONT>rng = .Range(rng, rng.End(xlToLeft))

<FONT color=red>Set </FONT>rng = .Range(rng, rng.End(xlToLeft))

rng.Copy Sheets("Results").[A1]

<FONT color=red>Set </FONT>rng = .[AD5].End(xlDown)

rng.Value = ""

rng.End(xlDown).Value = ""

<FONT color=red>End With</FONT>

<FONT color=red>Case </FONT>2 <FONT color=red>To </FONT>7

Sheets("Results").Select

<FONT color=red>Case </FONT><FONT color=red>Else</FONT>

Sheets("Form").Select

<FONT color=red>End Select</FONT>

<FONT color=red>Set </FONT>rng =<FONT color=red> Nothing</FONT>

<FONT color=red>End Sub</FONT>




</PRE>

 

[Juan Pablo González]

Does it crash always ? or only when the macro is run from a commandbutton? what's the active sheet ? have you tried stepping through the code to see if the sheet is being selected ? is the sheet name exactly that ? (Not case sensitive) Which versions have you tested that crash ?

 

[Elemental]

that's some nice code

it still dies on the cells.clear though

is it fussy enough to examine other subs and crash if they have too many sheet changes in them ?

 

[Elemental]

ok, i recorded the code with a marco after coding it in VBA, to see if that's it's preferred way of doing it, it ran ok in the macro as a section,but wont run altogether in VBA.

i've tested office 97 on NT and Office XP on WinXP.

the error is always, the sheet names are exact, and the problem changes with the method i use, kinda like theres no way out.

i havnt stepped into it as its not a macro and it just crashed after stopping the debug ( kinda like it works itself up to the point of becoming unstable ( maybe a mem leak if they still exist )