Range selection

[Guest]

Can anyone help with this Ver 8.0 Excel problem?

When running this code it fails at the Select line even though "Bingo" is a
valid Worksheet in the Workbook :-

Dim sht As WorkSheet

Set sht = Worksheets("Bingo")
With sht
.Range(Cells(1, 2), Cells(1, 2)).Select
End With

Does it need ActiveWorkbook in front of Worksheets?

Thanks.

 

[PCLIVE]

Try this:

Set sht = Sheets("Bingo")
With sht
.Range(Cells(1, 2), Cells(1, 2)).Select
End With

 

[PCLIVE]

Ok.

That was stupid of me.

What range are you trying to select? Is it B1? Is there supposed to be
some sort of range of cells? The 'Cells portions are the same. But I'm not
sure what you're trying to do with that statement. Please elaborate.

Regards.
Paul

 

[Dave Peterson]

First, you can only select a range on the active sheet.

Second, those cells() references are unqualified. If your code is in a General
module, then they'll refer to the activesheet--which may not be Bingo.

I'd do:

Dim sht As WorkSheet
Set sht = Worksheets("Bingo")
With sht
.select
.Range(.Cells(1, 2), .Cells(1, 2)).Select
End With

notice the dots in front of cells(). That means that they refer to the object
in the previous With statement--in this case sht (aka worksheets("Bingo")).

 

[Guest]

Thanks Paul/Dave - I think that will solve it - I'll test tomorrow. The key
as you say is to declare the sheet active. Paul - the range is indeed 1 cell
but I will change this to a proper range once the general syntax is fixed.

Cheers,
Andy.