Find and Replace Macro -Runs Slow

[scottsacha]

I have created the folling macro below. It runs find untill it gets about half way through all the sheets. Then it starts not responding for a couple minutes and then it goes back to running. I do not understand what is causing it to go to a not responding status.
Sub VersionChange()
Dim Sheet As Worksheet
Dim act1 As String
Dim act2 As String
Dim For1 As String
Dim For2 As String
Dim ws As Worksheet


act1 = Worksheets("Ref+").Range("Actual1")
act2 = Worksheets("Ref+").Range("Actual2")
For1 = Worksheets("Ref+").Range("Forecast1")
For2 = Worksheets("Ref+").Range("Forecast2")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each Sheet In ActiveWorkbook.Sheets
Set ws = Sheets(Sheet.Name)

If Right(Sheet.Name, 1) = "+" Then
Application.StatusBar = "Finished " & Sheet.Name & " Sheet"
Else
ws.Range(act1 & ":" & act2).Replace What:="($D", Replacement:="($A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(act1 & ":" & act2).Replace What:=",$E", Replacement:=",$B", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(For1 & ":" & For2).Replace What:="($A", Replacement:="($D", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
ws.Range(For1 & ":" & For2).Replace What:=",$B", Replacement:=",$E", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
Application.StatusBar = "Finished " & Sheet.Name & " Sheet"
End If
Next


Application.StatusBar = False
Application.ScreenUpdating = True
Sheets("Titles").Select
StatusMsgBox
Application.Calculation = xlCalculationAutomatic
Calculate
MsgBox "Update Complete"
End Sub
Sub StatusMsgBox()
CreateObject("WScript.Shell").Popup _
"Excel is starting to Calculate the Cells", 10, "ATTENTION"
End Sub

[Russell Hauf]

Think it's in the way you select your ranges...but at any rate, I think I can help you. Do you use Excel 2000 or 97 (or another version)?

-rh

[Dave Hawley]

Hi scottsacha

Firsly I wouldn't bother writting to the Status bar as code like this should run in a second of 2. Even with many Worsheets. All you are doing is adding more work for the macro.

You may also find you have an Event that is being triggered via the macro running, ie Workbook or Worksheet Event. Put:

Application.EnableEvents=False as the first line and then set it back to true.

[scottsacha]

I am using office 2000

[Dave Hawley]

On 2002-03-19 04:54, scottsacha wrote:
I am using office 2000

Yes, so am I! Your reason for telling me this is....

[scottsacha]

I was replying to the question above your reply.