?
Solved

Excel Find/Replace very slow

Posted on 2013-02-04
11
Medium Priority
?
2,570 Views
Last Modified: 2013-02-08
Experts,
I have an Excel worksheet loaded with linking formula's to other spreadsheets.  This totals my departments monthly progress.  My issue is that I need to change the formula's each month to point to the correct worksheets.  I assumed find and replace would do the trick but it is extremely slow and locks up.  

I am thinking that using a marco to swapout my formulas might be better.

So I am looking to search the entire worksheet searching for "Dec_" and swapping for "Jan_"

Can anyone assist with writing me a macro to do so?

Worksheet goes from A1 to U73

Thanks,
Bob
0
Comment
Question by:RobertStamm
  • 5
  • 5
11 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38852692
If you are changing more than a few values you should add

Application.ScreenUpdating = False

at the start of the code

and

Application.ScreenUpdating = True

at the end
0
 

Author Comment

by:RobertStamm
ID: 38852712
That will need included for sure.

Can you assist with code looping through worksheet?  I need assistance getting this setup.

Will need some type of instring to swapout month in formula.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38852718
Sure. Attach your workbook and explain what you need done.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50
ID: 38852725
Hello,

have you tried setting calculation to manual, then do the find and replace, then set calculation back to automatic?

cheers, teylyn
0
 

Author Comment

by:RobertStamm
ID: 38852753
I am looking to search the entire worksheet searching for "Dec_" and swapping for "Jan_"
2012-2013-Jan-Totals.xlsx
0
 

Author Comment

by:RobertStamm
ID: 38852763
Gang,
I have to go for now.  Will try any suggestions tomorrow.

Thanks all for the assistance.

Bob
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38852784
Sub ChangeLinks()
Dim rng As Range
Dim cell As Range
Dim strNewMonth As String
Dim strOldMonth As String
Set rng = ActiveSheet.Range("B4:U73")

Application.DisplayAlerts = False

strNewMonth = InputBox("Please enter the new 3-letter month", "Update Monthly Formulas")
If strNewMonth = "" Then
    MsgBox "No changes"
    Exit Sub
End If

For Each cell In rng
    strNewMonth = UCase(strNewMonth)
    Select Case strNewMonth
        Case "JAN"
            strOldMonth = "DEC"
        Case "FEB"
            strOldMonth = "JAN"
        Case "MAR"
            strOldMonth = "FEB"
        Case "APR"
            strOldMonth = "MAR"
        Case "MAY"
            strOldMonth = "APR"
        Case "JUN"
            strOldMonth = "MAY"
        Case "JUL"
            strOldMonth = "JUN"
        Case "AUG"
            strOldMonth = "JUL"
        Case "SEP"
            strOldMonth = "AUG"
        Case "OCT"
            strOldMonth = "SEP"
        Case "NOV"
            strOldMonth = "OCT"
        Case "DEC"
            strOldMonth = "NOV"
        Case Else
            MsgBox "Invalid month. Must be a month like 'Jan'"
            Exit Sub
    End Select
    If InStr(1, cell.Formula, strOldMonth, vbTextCompare) Then
        cell.Formula = Replace(cell.Formula, strOldMonth, strNewMonth, 1, , vbTextCompare)
    End If
Next

Application.DisplayAlerts = True

End Sub

Open in new window


We can also change the value in A3 and/or the year in the workbook referenced in the formula if you want.
0
 

Author Comment

by:RobertStamm
ID: 38865483
Martin,
This code works but it still takes a long time to run.  Seems like everytime it changes the link it goes out to the attached file and get an update before continuing.  I would think there would be a way to turnoff any link connection until the process is run.

Any additional thoughts anyone?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38865593
I know that when you open the workbook you can do this

Workbooks.Open Filename:="C:\MyWorkbook.xls", UpdateLinks:=False
0
 

Author Closing Comment

by:RobertStamm
ID: 38869077
This work well but it is still slow.  For my purposes this will work.  Thanks for all the help.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38869088
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question