Solved

Excel Find/Replace very slow

Posted on 2013-02-04
11
1,895 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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