Run time error '1004'

[Howard]

Hi,

I am using ExcelXP with service pack3 and I have a
sorting code that works like so when selecting a value
from a combo box. It works on my computer, but when
someone in a prior version of Excel tries to use it, they
get a run-time 1004 error. However, if I recreate the
code in their computer, verbatim, it works on their
computer. The run-time error breaks down starting
at "Selection.Sort". Does anyone know if this is some
kind compatibility issue or is there a way to better code
this?

Thanks - Howard

SortByTeaching:
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B10"),
Order1:=xlDescending, Key2:=Range( _
"A10"), Order2:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
GoTo CleanUp



Run time error '1004'
Application-defined or object-defined error

 

[Dave Peterson]

How do you run the macro?

Do you run it from a control from the controltoolbox toolbar? (Like a
commandbutton???).

If yes, then try changing the .takefocusonclick property to false for that
control.

If it doesn't have a .takefocusonclick property, then add:

activecell.activate

at the top of your code.

It was a bug in xl97 (fixed in xl2k).

If the prior version was xl2k, then this isn't the problem.

 

[Guest]

Dave,

Thanks for your response.

I created a combo box from the forms toolbar. I tried
creating a combo box from the control toolbox but the
sort code didn't work. I'm not a "pro" programmer so I
don't know why.

Anyway, I run the macro from a combo box created from the
forms toolbar. If I go into the properties setting of the
combo box, it only gives me options to format the control
such as size and protection.

I used the activecell.activate command and it didn't seem
to resolve the problem. I believe the error occurs when
the sort begins (Selection.Sort Key1:=Range("B10")...).
Excel doesn't seem to recognize the "range".

Any other suggestions would be appreciated.

 

[Dave Peterson]

The activecell.activate was only a suggestion if the control came from the
control toolbox toolbar--since yours came from the Forms toolbar, you can ignore
that suggestion.

I saw the 1004 error and jumped to the wrong conclusion.

I just looked at your code again and noticed this:

These two options: DataOption1:=xlSortNormal & DataOption2:=xlSortNormal
were added in xl2002.

xl2002 was enhanced to sort "text" numbers as numbers (or text).

You could remove them from your code:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B10"), Order1:=xlDescending, _
Key2:=Range("A10"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

May work.

======
But I'd still be concerned about working with the selections. If you know
what's selected, it might be easier to pick a column and a row and use that to
define your range. (And I don't like to let excel guess at the headers. If you
know you have them, use xlYes. If you know you don't, use xlNo.

I used column A and row 1 to find the lastcell to include in the range to be
sorted. And then I started in A1.

Option Explicit
Sub testme01()

Dim myRng As Range
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

With .Range("A1", .Cells(LastRow, LastCol))
.Sort key1:=.Range("b1"), order1:=xlDescending, _
key2:=.Range("a1"), order2:=xlAscending, _
header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End With

End Sub

(From your code, maybe row = 10.)

Sorry about not noticing those options yesterday.

 

[Guest]

Dave,

Thanks. I finally realized that the problem was not
really the sort code. Upon opening, I had an auto_open
code to fill a ComboBox. Then the sort code followed when
a user selects an item in the ComboBox.

The problem was the procedure to fill the ComboBox left
the cursor at the ComboBox. Thus, when the sort code
initiated, it errored because it couldn't find the sort
range to sort. So, I tossed in a [Range("A1").Select]
right before the sort command to reset the cursor on a
cell in the worksheet.

Also, I created a named range instead of selecting
everything to the right and bottom of the area I wanted
to sort. I selected the named range prior to the sort
command.

The code you gave me to select the last row and column
didn't work in this example because I had stuff below the
area I wanted to sort. When I tried it, it sorted
everything. It's still a great code I could use for
something else though.

You wouldn't have known given the information I provided.

Thanks - Howie

 

[Dave Peterson]

Glad you got it working.

Dave,

Thanks. I finally realized that the problem was not
really the sort code. Upon opening, I had an auto_open
code to fill a ComboBox. Then the sort code followed when
a user selects an item in the ComboBox.

The problem was the procedure to fill the ComboBox left
the cursor at the ComboBox. Thus, when the sort code
initiated, it errored because it couldn't find the sort
range to sort. So, I tossed in a [Range("A1").Select]
right before the sort command to reset the cursor on a
cell in the worksheet.

Also, I created a named range instead of selecting
everything to the right and bottom of the area I wanted
to sort. I selected the named range prior to the sort
command.

The code you gave me to select the last row and column
didn't work in this example because I had stuff below the
area I wanted to sort. When I tried it, it sorted
everything. It's still a great code I could use for
something else though.

You wouldn't have known given the information I provided.

Thanks - Howie

-----Original Message-----
The activecell.activate was only a suggestion if the control came from the
control toolbox toolbar--since yours came from the Forms toolbar, you can ignore
that suggestion.

I saw the 1004 error and jumped to the wrong conclusion.

I just looked at your code again and noticed this:

These two options: DataOption1:=xlSortNormal & DataOption2:=xlSortNormal
were added in xl2002.

xl2002 was enhanced to sort "text" numbers as numbers (or text).

You could remove them from your code:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B10"), Order1:=xlDescending, _
Key2:=Range("A10"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

May work.

======
But I'd still be concerned about working with the selections. If you know
what's selected, it might be easier to pick a column and a row and use that to
define your range. (And I don't like to let excel guess at the headers. If you
know you have them, use xlYes. If you know you don't, use xlNo.

I used column A and row 1 to find the lastcell to include in the range to be
sorted. And then I started in A1.

Option Explicit
Sub testme01()

Dim myRng As Range
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End (xlToLeft).Column

With .Range("A1", .Cells(LastRow, LastCol))
.Sort key1:=.Range("b1"), order1:=xlDescending, _
key2:=.Range("a1"), order2:=xlAscending, _
header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End With

End Sub

(From your code, maybe row = 10.)

Sorry about not noticing those options yesterday.



--

Dave Peterson
(e-mail address removed)
.