Solved

Excel Find/Replace very slow

Posted on 2013-02-04
11
2,157 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 48

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 48

Expert Comment

by:Martin Liss
ID: 38852718
Sure. Attach your workbook and explain what you need done.
0
Technology Partners: 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 48

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 48

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 48

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

695 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