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?

--
Regards

goconnor

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

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

--
Regards

goconnor


"goconnor" wrote:

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?

--
Regards

goconnor

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 wrote:

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?

--
Regards

goconnor

--

Dave Peterson

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


--
Regards

goconnor


"Dave Peterson" wrote:

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 wrote:

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?

--
Regards

goconnor

--

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?



goconnor wrote:

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

--
Regards

goconnor

"Dave Peterson" wrote:

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 wrote:

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?

--
Regards

goconnor

--

Dave Peterson


--

Dave Peterson

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.

--
Regards

goconnor


"Dave Peterson" wrote:

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?



goconnor wrote:

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

--
Regards

goconnor

"Dave Peterson" wrote:

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 wrote:

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?

--
Regards

goconnor

--

Dave Peterson


--

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!)

goconnor wrote:

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.

--
Regards

goconnor

"Dave Peterson" wrote:

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?



goconnor wrote:

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

--
Regards

goconnor

"Dave Peterson" wrote:

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 wrote:

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?

--
Regards

goconnor

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson