none
Search and Replace within an Excel Formula

    Question

  • I have a rather large Excel workbook in which I need to replace a portion of a formula with a reference to another drive.  I literally have hundreds of these to change on multiple worksheets.  As others have found before me, the Search and Replace command will not work within a formula.  I have also tried to take the spreadsheet to an XML editor which didn't work either.  Reading the forums, I've pieced together the following code.  Unfortunately, it doesn't work.  The specific change I'm trying to make is from "C:\Data" to "P:\Data".  Thanks in advance for any help and insight in troubleshooting.

    Mike

    Sub ReplaceCData()
    '
    ' ReplaceCData Macro
    ' Replaces C:\Data with P:\Data
    '
      Do While Not ActiveCell.Value = ""
      
      Dim txtFormula As String
      
        txtFormula = ActiveCell.FormulaArray
        txtFormula = Replace(txtFormula, "C:\Data", "P:\Data")
        ActiveCell.FormulaArray = txtFormula
      
        Call ReplaceCData
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
      Loop
    
    End Sub

    Thursday, October 14, 2010 3:41 PM

Answers

  • Once you have your coding working on a single sheet, try something like:

     

     

    Sub routine()
    Dim wks As Worksheet
    For Each wks In Worksheets
        wks.Activate
        Range("A1").Select
        Call ReplaceCData
    Next
    End Sub

     

    Instead of A1, use the initial ActiveCell you want.


    GSNU30001
    Friday, October 15, 2010 9:42 AM
  • An alternative to editing the formula using VBA.

    As the formula is a link to another file.  Have you tried editing the links in the workbook, using the menus/ribbon.  You can simply repoint the old linked file to the new linked file, and Excel will do the rest.

    You can find the command in

    Pre 2007 Edit menu, Links

    Post 2007 Data tab, Edit links.

     


    G North MMI
    Friday, October 15, 2010 11:14 AM

All replies

  • It is not clear from the post if you want to change only array formulas or all formulas on a worksheet.  For array formulas only try:

     

     

    Sub FormulaFixer()
    Dim rF As Range, r As Range
    Dim s1 As String, s2 As String
    s1 = "C:\"
    s2 = "P:\"
    Set rF = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
    For Each r In rF
        If r.HasArray Then
            r.FormulaArray = Replace(r.FormulaArray, s1, s2)
        End If
    Next
    End Sub


    GSNU30001
    Thursday, October 14, 2010 6:36 PM
  • Hi

    The code you are using will only work with array formulae, and assumes the formulae are in blocks separated by blank rows.  There is no need to Call ReplaceCData within this code.

    If your formulae are not array formulae and all in the same column then you need to replace FormulaArray with Formula and the two lines Selection.end(xldown).select with one ActiveCell.Offset(1,0).Activate.  This will cause the code to visit each cell in the column until the empty cell below you list is reached.

    Obviously the code needs to be run with the first row containing the formula selected.

    Hope this is of use.

    Friday, October 15, 2010 7:50 AM
  • Thank you both for your responses.  It's not an array or a column, but multiple worksheets (a dozen??) across an entire workbook.  I had played with what I had to the point I could assign it to a button and do one cell at a time without going in and manually typing the change.  G North, I'll make the changes you suggest and will see if I can do an entire column.  Ultimately, I'd like to come up with a macro that would at least do a worksheet, if not the entire workbook.

    Thanks again for both of your responses ... very much appreciated!  Additional insight to get me to being able to make the change across the entire workbook is similarly appreciated.

    Friday, October 15, 2010 8:47 AM
  • Once you have your coding working on a single sheet, try something like:

     

     

    Sub routine()
    Dim wks As Worksheet
    For Each wks In Worksheets
        wks.Activate
        Range("A1").Select
        Call ReplaceCData
    Next
    End Sub

     

    Instead of A1, use the initial ActiveCell you want.


    GSNU30001
    Friday, October 15, 2010 9:42 AM
  • An alternative to editing the formula using VBA.

    As the formula is a link to another file.  Have you tried editing the links in the workbook, using the menus/ribbon.  You can simply repoint the old linked file to the new linked file, and Excel will do the rest.

    You can find the command in

    Pre 2007 Edit menu, Links

    Post 2007 Data tab, Edit links.

     


    G North MMI
    Friday, October 15, 2010 11:14 AM