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

Question

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!!

Answer 1

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.