Find and Replace Macro -Runs Slow

scottsacha

New Member
Joined
Mar 17, 2002
Messages
3
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top