ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Range class failed (https://www.excelbanter.com/excel-programming/354382-range-class-failed.html)

Richard
Range class failed
 
Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

Dave Peterson
Range class failed
 
What happens wrong?



Richard wrote:

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

--

Dave Peterson

Bob Phillips[_6_]
Range class failed
 
Richard,

Exactly what problem are you having, as it seems to work fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Richard" wrote in message
...
Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

Richard
Range class failed
 
I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.



"Dave Peterson" wrote:

What happens wrong?



Richard wrote:

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

--

Dave Peterson

Richard
Range class failed
 
I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.

"Bob Phillips" wrote:

Richard,

Exactly what problem are you having, as it seems to work fine for me.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Richard" wrote in message
...
Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

Dave Peterson
Range class failed
 
I'd start by adding:

debug.print rng.address

to see what that shows. Drop it in a couple of spots. And see where it errors
out.


Richard wrote:

I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.

"Dave Peterson" wrote:

What happens wrong?



Richard wrote:

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

--

Dave Peterson


--

Dave Peterson

Richard
Range class failed
 
I've now figured out that it's comming from the CommandButton1 when I click
it from the "HList" Sheet, Code sorts ok when I run code from vba, any
suggestions?

"Dave Peterson" wrote:

I'd start by adding:

debug.print rng.address

to see what that shows. Drop it in a couple of spots. And see where it errors
out.


Richard wrote:

I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.

"Dave Peterson" wrote:

What happens wrong?



Richard wrote:

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

--

Dave Peterson


--

Dave Peterson

Dave Peterson
Range class failed
 
I initially tried it with a button on one worksheet that sorted a different
worksheet and your code worked fine in my tests in xl2003.

Are you running xl97?

If that's the case, change the .takefocusonclick property to false for that
commandbutton.

Show the control toolbox toolbar.
click on the design mode icon
rightclick on the commandbutton
choose properties
change .takefocusonclick to false
click on the design mode icon (to get out of that mode)

Alternatively, you could add this to the top of your procedu

Activecell.activate

(This bug was fixed in xl2k.)

Richard wrote:

I've now figured out that it's comming from the CommandButton1 when I click
it from the "HList" Sheet, Code sorts ok when I run code from vba, any
suggestions?

"Dave Peterson" wrote:

I'd start by adding:

debug.print rng.address

to see what that shows. Drop it in a couple of spots. And see where it errors
out.


Richard wrote:

I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.

"Dave Peterson" wrote:

What happens wrong?



Richard wrote:

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Richard
Range class failed
 
Thanks that was it, yes I'm using 97, Thanks so much

"Dave Peterson" wrote:

I initially tried it with a button on one worksheet that sorted a different
worksheet and your code worked fine in my tests in xl2003.

Are you running xl97?

If that's the case, change the .takefocusonclick property to false for that
commandbutton.

Show the control toolbox toolbar.
click on the design mode icon
rightclick on the commandbutton
choose properties
change .takefocusonclick to false
click on the design mode icon (to get out of that mode)

Alternatively, you could add this to the top of your procedu

Activecell.activate

(This bug was fixed in xl2k.)

Richard wrote:

I've now figured out that it's comming from the CommandButton1 when I click
it from the "HList" Sheet, Code sorts ok when I run code from vba, any
suggestions?

"Dave Peterson" wrote:

I'd start by adding:

debug.print rng.address

to see what that shows. Drop it in a couple of spots. And see where it errors
out.


Richard wrote:

I know it's weard, It works in my other workbooks but not this one. It's
just giving me a Range Class Failed warning.

"Dave Peterson" wrote:

What happens wrong?



Richard wrote:

Having problem with this code:
Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = "Sort"
Dim rng As Range, rng1 As Range
With Worksheets("HList")
Set rng = .Range(.Cells(3, "A"), .Cells(Rows.Count, "A").End(xlUp))
Set rng1 = .Cells(3, "A").End(xlToRight)
Set rng = rng.Resize(, rng1.Column)
rng.Sort _ 'Having problem here I think
Key1:=.Range("A3"), _ 'Having problem here
Order1:=xlAscending, _ 'Having problem herre
Header:=xlNo 'Having problem here
End With
Application.OnTime Now + TimeSerial(0, 0, 2), _
ThisWorkbook.Name & "!ResetCaption"
CommandButton1.Caption = "Sorting..."
End Sub
Sub ResetCaption()
If Not Sheet2 Is Nothing Then
Sheet2.CommandButton1.Caption = "Sort"
End If
End Sub
Thanks in advance!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 12:03 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com