Excel 2003 Macro-replace part of formula from value in cell

February 24, 2012 at 21:54:59
Specs: Windows XP
Not sure i can describe what i want to do correctly. I want to take a value in one cell and update many formulas with this value.
Cell C4="BK"
Cell C5="BJ"

I want to update all formulas (i.e., Sum(BK100:BK200)) that contain BK in a named range by replacing them with BJ So new formula after running macro would be Sum(BJ100:BJ200)


See More: Excel 2003 Macro-replace part of formula from value in cell

Report •

#1
February 24, 2012 at 22:24:11
ok. i think i figured this out...
Range("NamedRange").Select
ActiveWindow.ScrollColumn = 1
Selection.Replace What:=Range("c4").Value, Replacement:=Range("c2").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=Range("c5").Value, Replacement:=Range("c3").Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Report •

#2
February 25, 2012 at 09:53:43
Simpler way is to use Search & Replace on the task bar.

Just Highlight your target cells,
Select Find & Select on the Ribbon
Select Replace

Find What: BK
Replace With: BJ

Works for me.

MIKE

http://www.skeptic.com/


Report •

#3
February 25, 2012 at 10:43:39
Or, a simpler version of your code follows...

Rarely, if ever, do you have to select an object (Range, etc.) within VBA in order to perform an operation on it. For example.

  Range("NamedRange").Select
   Selection.ClearContents

Can be replaced with:

Range("NamedRange").ClearContents

Further, consider using the With method when performing more than one operation on the same object, as I do below.

Sub FixFormulas()
  With Range("NamedRange")
    .Replace What:=Range("C4").Value, _
       Replacement:=Range("C2").Value, LookAt:=xlPart
    .Replace What:=Range("C5").Value, _
       Replacement:=Range("C3").Value, LookAt:=xlPart
   End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question