Run-time error '1004'

[goconnor]

I have a macro (Excel 2003) which goes to a range (Range 1) of four columns
and 100 rows to copy to another range (Range 2). The first column of Range 1
contains bandomly-generated numbers (=RAND()). I have been using this file
for quite some time without any problem, as have a number of other people.
One user has reported that he is receiving a Run-time error '1004' each time
he tries to execute the macro. I have read the MS info re this error which
says:

"This issue may occur if one or more of the cells in an array (range of
cells) contain a character string that is set to contain more than 911
characters."

I have had trouble following the MS workaround which seems incredibly
complex. I have formated the random number column to ensure no more than 10
digits after the decimal point.

Can anyone offer any practical solutions to this issue?

 

[goconnor]

I should have specified the type of run-time error. In full:

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

 

[Dave Peterson]

That 1004 error shows up in lots and lots of situations.

Can you post the relevant code and indicate the line that causes the error?

 

[goconnor]

Many thanks, Dave. Since I didn't have the problem myself, I can only go by
the screenshot of the error box which my correspondent sent to me. Based on
that shot, it looks like the problem occurred when the macro was executing a
sort by the random number column (AC). Here is that part of the code covering
that operation. ((I'm no coder, but I suspect much of the ActiveWindow ...
code is redundant?)

Application.Goto Reference:="Times_tables"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.SmallScroll Down:=18
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
Range("AC62001:AF62144").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("AC62001"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Dave Peterson]

The activewindow stuff won't hurt (or help) your code.

Application.Goto Reference:="Times_tables"
Range("AC62001:AF62144").Select
Selection.Sort Key1:=Range("AC62001"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Are you both using the same version of excel?

DataOption# was added in xl2002 and will cause an error in earlier versions. I
don't recall if it's a 1004 error, though.

Try deleting that portion (and the previous comma and underscore, too).

======
My second guess would depend on where this code is located. Is it in a General
module or under a worksheet?

Many thanks, Dave. Since I didn't have the problem myself, I can only go by
the screenshot of the error box which my correspondent sent to me. Based on
that shot, it looks like the problem occurred when the macro was executing a
sort by the random number column (AC). Here is that part of the code covering
that operation. ((I'm no coder, but I suspect much of the ActiveWindow ...
code is redundant?)

Application.Goto Reference:="Times_tables"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.SmallScroll Down:=18
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
Range("AC62001:AF62144").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("AC62001"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[goconnor]

Thanks again, Dave. Apologies for any delay - I'm based in Australia. I'm not
sure what version was being used by the person who had the problem - I'll
check that today. The code is included under general modules - i.e. it's
under the Modules directory rather than the Microsoft Excel Objects directory
in VBAProject.

I have removed the DataOption code as you suggested and don't seem to have
lost any functionality in 2003. Is there any reason this should cause a
problem in 2003 or 2007?

Thanks again for your help.

 

[Dave Peterson]

Since you have xl2003, you can read what that parm does in VBA's help.

Everything I've ever done (as far as I can recall) has used the default. On the
other hand, your requirements may differ. (I bet not!)

Thanks again, Dave. Apologies for any delay - I'm based in Australia. I'm not
sure what version was being used by the person who had the problem - I'll
check that today. The code is included under general modules - i.e. it's
under the Modules directory rather than the Microsoft Excel Objects directory
in VBAProject.

I have removed the DataOption code as you suggested and don't seem to have
lost any functionality in 2003. Is there any reason this should cause a
problem in 2003 or 2007?

Thanks again for your help.

 

[goconnor]

Sorry, I'm not sure what you mean when you say that you've always used the
default. Does that mean without the DataOption# and that it doesn't matter?

Also, this is probably very basic, but I don't know what you mean by "parm".
I searched for DataOption in VBA help, but got no results.

Apologies - I'm not a coder, just a humble, as-needed macro writer!

 

[Dave Peterson]

Parm is short for parameter.

And if you search VBA's help for "Sort" (and click on Sort Method), you'll see
this:

DataOption1 Optional XlSortDataOption. Specifies how to sort text in key 1.
Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treat text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

I've always use the default (sort numbers and text separately).

You could sort one way if the user is running xl2002+ and another if the user is
using xl2k and below. But I bet it's not worth the trouble.

=====
You'll see the equivalent if you sort a range manually:

MS added an extra warning dialog when your field contains text that looks like
numbers.

Put this in A1:A4
1
2
'3
'4

(the last two have apostrophes before the number)

Then sort A1:A4. You'll be prompted to find out how those text numbers should
be treated.

Sorry, I'm not sure what you mean when you say that you've always used the
default. Does that mean without the DataOption# and that it doesn't matter?

Also, this is probably very basic, but I don't know what you mean by "parm".
I searched for DataOption in VBA help, but got no results.

Apologies - I'm not a coder, just a humble, as-needed macro writer!