Solved

Excel Find/Replace very slow

Posted on 2013-02-04
11
2,082 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 47

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 47

Expert Comment

by:Martin Liss
ID: 38852718
Sure. Attach your workbook and explain what you need done.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 47

Accepted Solution

by:
Martin Liss earned 500 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 47

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 47

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 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