I am running a routine in Excel 2003 which takes minutes
to execute. The same routine in Excel-97 is exceuted
instandly showing no measurable delay.

The routine:

Set q1 = Workbooks(wbi).Sheets(wsi).Range(s1)
Set q3 = Workbooks(wbi).Sheets(wsi).Range(q1, q1.End
xlDown))
q3.Offset(0, 10).Select
For Each cell In Selection
cell.Value = cell.Value * 1
Next cell

The input file is a flat text file with 1000 records (rows)
I want to be sure that the data in collumn 10 is numeric
when copied. Previously in the coding I replaced some
decimal points for decimal comma's. This is the reason to
multply by 1.

It is the for/next loop (1000 cells) which takes 2
minutes on a 2,6 Ghz system.

regards,
Erik

Your code flies for me in Excel 2003. It isn't an issue of having calc mode
set to automatic by any chance?

--
Jim Rech
Excel MVP
"Erik Creyghton" wrote in message
...
|I am running a routine in Excel 2003 which takes minutes
| to execute. The same routine in Excel-97 is exceuted
| instandly showing no measurable delay.
|
| The routine:
|
| Set q1 = Workbooks(wbi).Sheets(wsi).Range(s1)
| Set q3 = Workbooks(wbi).Sheets(wsi).Range(q1, q1.End
| xlDown))
| q3.Offset(0, 10).Select
| For Each cell In Selection
| cell.Value = cell.Value * 1
| Next cell
|
| The input file is a flat text file with 1000 records (rows)
| I want to be sure that the data in collumn 10 is numeric
| when copied. Previously in the coding I replaced some
| decimal points for decimal comma's. This is the reason to
| multply by 1.
|
| It is the for/next loop (1000 cells) which takes 2
| minutes on a 2,6 Ghz system.
|
| regards,
| Erik

I just recorded this
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/30/2004 by Don Guillett
'

'
Range("H2").Select
ActiveCell.FormulaR1C1 = "1"
Selection.Copy
Range("F10:F11").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
End Sub
which can be reduced to. Change to suit
Sub Macro1()
Range("H2") = "1"
Range("H2").Copy
Range("F1:F11").PasteSpecial , Operation:=xlMultiply
End Sub


--
Don Guillett
SalesAid Software

"Erik Creyghton" wrote in message
...
I am running a routine in Excel 2003 which takes minutes
to execute. The same routine in Excel-97 is exceuted
instandly showing no measurable delay.

The routine:

Set q1 = Workbooks(wbi).Sheets(wsi).Range(s1)
Set q3 = Workbooks(wbi).Sheets(wsi).Range(q1, q1.End
xlDown))
q3.Offset(0, 10).Select
For Each cell In Selection
cell.Value = cell.Value * 1
Next cell

The input file is a flat text file with 1000 records (rows)
I want to be sure that the data in collumn 10 is numeric
when copied. Previously in the coding I replaced some
decimal points for decimal comma's. This is the reason to
multply by 1.

It is the for/next loop (1000 cells) which takes 2
minutes on a 2,6 Ghz system.

regards,
Erik

Not sure, so just speculating he I don't see how a
loop with 1000 iterations would last that long on its
own. First, are you absolutely sure you are only getting
1000 cells from your For Each Cell in Selection?

Assuming yes, then the reason for the long execution time
is not likely to be in the number of iterations but rather
what is being processed during the loop:

cell.Value = cell.Value * 1

So why would this take so long? Two possibilities come to
mind:

1) It is taking a while to find cell.value. This would be
an internal VBA issue and could be due to changes in how
VBA handles internal procedures in the update from 97 to
2003. I am not aware of any issues but it is possible
that in expanding the functionality of the object model it
either slowed the evaluation of "For Each Cell in..." or
in evaluating and returning the cell values.

2) cell.Value has to be treated as a Variant. Then VBA
has to spend time figuring out how to
interpret "cell.Value * 1" - different methods of
evaluation would be needed if cell.value was an integer,
or a Single, or a Double, ...

To remedy (assuming one of these issues is the reason for
slowness):

1) Try an alternative way of stepping through your cells -
for example, count the rows and columns and set up your
own loop explicitly.

2) Create an explicitly defined variable of the proper
type to read cell.value, and then do the math, e.g:
Dim CellVal as Single
....
CellVal = cell.value
CellVal = CellVal * 1

I may be way off base, but just trying to help
troubleshoot for you. Hope it helps...

-----Original Message-----
I am running a routine in Excel 2003 which takes minutes
to execute. The same routine in Excel-97 is exceuted
instandly showing no measurable delay.

The routine:

Set q1 = Workbooks(wbi).Sheets(wsi).Range(s1)
Set q3 = Workbooks(wbi).Sheets(wsi).Range(q1, q1.End
xlDown))
q3.Offset(0, 10).Select
For Each cell In Selection
cell.Value = cell.Value * 1
Next cell

The input file is a flat text file with 1000 records
(rows)
I want to be sure that the data in collumn 10 is numeric
when copied. Previously in the coding I replaced some
decimal points for decimal comma's. This is the reason to
multply by 1.

It is the for/next loop (1000 cells) which takes 2
minutes on a 2,6 Ghz system.

regards,
Erik
.

I will try your suggestions Monday first thing.
I do not have the test file at home.

Calculation was set to manual in both cases (Excel-97 / 2003). I will try to
isolate the conditions and report back.

Thanks for your help sofar,
Erik