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

Question

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"

Answer 1

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

Simply looping 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

I would recommend making a backup of the workbook before running the macro.