Solved

Excel Find/Replace very slow

Posted on 2013-02-04
11
1,840 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 46

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 46

Expert Comment

by:Martin Liss
ID: 38852718
Sure. Attach your workbook and explain what you need done.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 46

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 46

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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now