Did you try the code mod I supplied? Specifically the part with

myDateRange.offset(#,#).Value = ... whatever you want?

I've seen that speed up execution.
--
HTH,
Barb Reinhardt




"XP" wrote:


Good catch! Yes indeed, I should have used cCell in place of activecell.

My bad.

I can also then remove "Activecell.Offset(0, 1).Select" at the bottom.

My code is still running slow though...


"Barb Reinhardt" wrote:

also, might the activecell address and the ccell address be the same?

Barb Reinhardt

"XP" wrote:


Here is a function, in an earlier call, I kill screenupdating, calculation,
turn off automatic page breaks, and ensure "normal" view is on...

Private Function CalculateValues()
'write the values into the sheet:
Dim cCell As Range
Dim lRowsORA As Long
Dim lRowsUMS As Long
lRowsORA = Worksheets(gcsSheetGL).UsedRange.Rows.Count
lRowsUMS = Worksheets(gcsSheetAR).UsedRange.Rows.Count
Worksheets(gcsSheetRPT).Activate
Range(mcsAnchor).Activate
For Each cCell In ActiveSheet.UsedRange.Rows(1).Columns
If IsDate(Cells(1, ActiveCell.Column).Value) Then
ActiveCell.Offset(1, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetGL & "'!C2:C" & lRowsORA & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetGL & "'!B2:B" & lRowsORA & "0,'" & gcsSheetGL &
"'!B2:B" & lRowsORA & ")))")
ActiveCell.Offset(2, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetGL & "'!C2:C" & lRowsORA & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetGL & "'!B2:B" & lRowsORA & "<0,'" & gcsSheetGL &
"'!B2:B" & lRowsORA & ")))")
ActiveCell.Offset(5, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetAR & "'!C2:C" & lRowsUMS & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetAR & "'!B2:B" & lRowsUMS & "0,'" & gcsSheetAR &
"'!B2:B" & lRowsUMS & ")))")
ActiveCell.Offset(6, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetAR & "'!C2:C" & lRowsUMS & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetAR & "'!B2:B" & lRowsUMS & "<0,'" & gcsSheetAR &
"'!B2:B" & lRowsUMS & ")))")
End If
ActiveCell.Offset(0, 1).Select
Next cCell
End Function


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


In particular, my current project code uses "For Each Cell...Next" to
evaluate an array formula and then writes the values into the appropriate
cells.

This was a blur in 2003; in 2007 you can see each cell update with a couple
seconds in between...


"Barb Reinhardt" wrote:

I've also noticed that there are times when I can speed up For Next code in
other ways, but I'd have to see your code to see if it's helpful.

Barb Reinhardt

"XP" wrote:


Hi Barb,

Thanks for your reply, but I must not have been clear in my original post. I
KNOW how to code, and in Office 2003 my code was optimized and ran superbly.
It looks like I'm already using most of Mr. Pearson's suggestions and have
been for years...

The problem is in Office 2007 the same code runs extremely slowly...THAT is
the problem: SPEED.

Please post back if there is anything I can do to Office 2007 to make it run
faster; or certain code structures to avoid if speed is an issue, etc.

Thanks!

"Barb Reinhardt" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"XP" wrote:

Using Office XP with Office 2007.

I have a severe slow down in my code when using any For Each...Next
structure, such as "For Each Cell" or "For x = 1 to 10000"...

This slow down seems to be only in Office 2007. In 2003, my code runs like a
machine gun; in 2007 it's more like a burp, wait a second, burp, wait a
second...etc.

This is the same code on the same machine so I'm sure my code is not to
blame since it has always run fine in 2003. At this pace, a program that used
to run in a few seconds could now take several minutes.

Has anyone got a fix or any ideas how to make it run faster?

Thanks much for your assistance/guidance.