Replace all instances of something in Excel formulas in the entire book using VBA?-CodePudding

In every formula of every cell of every sheet, the entire book, I want to replace part of the formula string, changing all " " to "-", is there an easy way to do this in VBA if I have a particular workbook object xlWb?

I am trying:

xlWb.Cells.Replace What:=" ", Replacement:="-", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

but get "Object does not support this property or method"

CodePudding user response：

A workbook has not cells, only worksheets have. This causes your runtime error. Your Replace-command itself looks okay to me.

Simply loop over all the worksheets of your workbook should do the trick:

Dim ws As Worksheet
For Each ws In xlWb.Worksheets
    ws.Cells.Replace What:="-", Replacement:=" ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
        , FormulaVersion:=xlReplaceFormula2
Next

(If I where you, I would make a backup of the workbook before running the macro...)