Windows ME Run Time Error 438 1

[purhaze]

Hi
If anyome can help with the following it wwould be appreciated

The following works OK using Windows 2003 however when it is run using Windows ME (or 98) a Run Time Error 438 occurs

The problem occurs in the line:
Set tbl = Worksheets("Pool").Range("a3").CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2, tbl.Columns.Count).Select


Any thoughts on how to fix this sos that it can be run using Windows ME would be welcome

Yhe code is:

Private Sub CommandButton1_Click()
'Calculate Winner and rehandicap
Dim NumberPlayers As Integer
Dim counter As Integer
Dim hcap As Single
Dim score As Integer

'select all p[layer rows excluding header row
Set tbl = Worksheets("Pool").Range("a3").CurrentRegion
tbl.Offset(2, 0).Resize(tbl.Rows.Count - 2, tbl.Columns.Count).Select

'sort selected area

Selection.Sort Key1:=Range("D3"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets("Pool").Cells(3, 3).Select
NumberPlayers = Application.WorksheetFunction.CountA(Worksheets("Pool").Range("A3:A10000")

 

[FXP]

In case you've already figured it out, Error 438 is:

"Object doesn't support this property or method"

I am not familiar with ME and don't have a copy so you'll have to figure out another way to create the data range. Can you create the range in the Excel workbook and retrieve it from Word?

Good luck.

 

[HughLerwill]

It could (probably) be the version of Ms Office/ Excel you are using rather than the OS version. What are the respective Excel version numbers?

 

[TonyJollans]

The problem is Excel version, not Windows version (whatever you mean by Windows 2003) and the offending code is:

Selection.Sort Key1:=Range("D3"), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom[red], _
        DataOption1:=xlSortNormal[/red]

Just remove the bit in red.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[HughLerwill]

Yes Tony, coincidentally I discovered that for myself today that option to Sort does not run in Excel versions <10 (Office XP).

regards Hugh,

 

[purhaze]

Hi All,
That was ny first post so thanks for bearing with the way the original question was worded -
anyway Tony's hint worked well wih the Sort procedure
- still had to put a line in to Dim the "tbl" as Object to get rid of the run time error
- anyway all's well and thanks for you feedback; sorry about the tardy response (there was a problem this end with borrowing and returning a notebook)
Anyway, Have a good one