CODEWITHSUNDEEP
vbaexcel
Ycombinatordev
Ycombinatordev

Reputation: 21

Excel How to replace one part of a Formula in all the cells in a Column

I am trying to replace ='Trial Balance'!E2+'Trial Balance'!E3 with ='Trial Balance'!C2+'Trial Balance'!C3 in Excel.

How do I change the formula in all the rows in a column from 'E' to 'C'?

I have written a VB Macro but I think it is wrong. Can somebody please correct it for me.

Sub RoundToZero2()
    For Each C In Worksheets("BalanceSheet").Range("BU8:BU103").Cells
        If E Then c.Value = C
    Next
End Sub

I also came across the SUBSTITUTE and REPLACE functions built into excel's formulas. Could I also use those?

Upvotes: 1

Views: 1658

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Use this

Worksheets("BalanceSheet").Range("BU8:BU103").Replace "'!E","'!C"

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152660

As long as you have no other Capital "E"s in your formula besides the ones you want to replace. This will do it

Sub RoundToZero2()
    Dim formstr As String
    For Each c In Worksheets("BalanceSheet").Range("BU8:BU103").Cells
        formstr = c.Formula
        formstr = Replace(formstr, "!E", "!C", , , vbBinaryCompare)
        c.Formula = formstr
    Next
End Sub

Upvotes: 0

Related Questions