Find and Replace Macro -Runs Slow
Find and Replace Macro -Runs Slow
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Find and Replace Macro -Runs Slow

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Atlanta, Georgia
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.




  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Atlanta, Georgia
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using office 2000

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-19 04:54, scottsacha wrote:
    I am using office 2000
    Yes, so am I! Your reason for telling me this is....




  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    Atlanta, Georgia
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I was replying to the question above your reply.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com