Is there any workaround to replace formulae in many cells in a worksheet

1
votes

I have an excel sheet with 1,373,760 used cells and each of them have a formula inside it unique in their own way. But every cell refers to a file path, which needs to be changed. Search and replace is taking huge amount of time and fails often at certain places. Is there an efficient way to replace these cells?

Sample formulae: '\root\folder\subfolder\another_folder[workbook_name]worksheet_name'cellNumber

Workbook, Worksheet, cell number are unique. Only the path is constant.

I tried referring to other cells by storing path and sheet names, but it's not working that way: Reference another workbook with dynamic worksheet name

All these cells are populated using VBA which took around 15 hours. So any efficient way to create the new workbook is appreciated as well.

Thanks in advance!!

2
excelreplaceexcel-formulaexcel-2013vba
General comment - You're pushing the limits of Excel, are you sure you need a page with 1.3 million formulas? In any case, I hate to even suggest it because it may cause the heat-death of the universe, but maybe find/replace on the formulas?? Can you give a few more examples of the different formulas and what you're trying to replace them to be? – BruceWayne
@BruceWayne I really don't have an option or opinion in this scenario. I have to make it work. The formula I gave in my question is the only format all the cells are following. – Pramod

2 Answers

2
votes

Something like this, depending on what exactly you are looking for:

Public Sub TestMe()

    Dim myCell  As Range

    For Each myCell In ActiveSheet.UsedRange
        If myCell.HasFormula Then
            If InStr(1, myCell, "\root\folder\subfolder\another_folder") Then
                myCell.Formula = "=root\folder\subfolder\another_folder" + something
            End If
        End If
    Next myCell

End Sub
  • Turning Off automatic calculations here is a good idea.
  • The code goes around each cell in the UsedRange of the ActiveSheet and in case that it is a formula, it checks whether it contains '\root\folder\subfolder\another_folder.
  • If this is the case, a new formula is generated.
1
votes

Search and replace from my humble opinion is your fastest solution, disable calculations via VBA or manually. after the replace has finished enable automatic calculations.

VBA code to enable and disable automatic calculations:

Sub Disbale_Automatic_Calc()
Application.Calculation = xlCalculationManual
End Sub

Sub Enable_Automatic_Calc()
Application.Calculation = xlCalculationAutomatic
End Sub