Resolve a cell reference in formula but keep constants

[mateo]

“The Problem”<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a group of salesmen ( SalesHoles to me ) who want to do something that I have never been asked before. Other suggestions I give them fall on deaf (and dumb) ears and ‘are not an option’<o></o>
<o> </o>
They have a column of formulas like this<o></o>
<o> </o>
=c2+2+1<o></o>
=c3+1+1<o></o>
=c4+3+1<o></o>
=c5+2+2<o></o>
…Etc…<o></o>
<o> </o>
“What the formulas means”<o></o>
The “C” column cell reference is what the price WAS in January.<o></o>
The 1<SUP>st</SUP> constant is the price increase they made April<o></o>
The 2<SUP>nd</SUP> constant is the price increase they made July<o></o>
<o> </o>
“What they want to do is…”<o></o>
Resolve all the cell references but KEEP the two constants.
The final result they want is this
=42+2+1<o></o>
=35+1+1<o></o>
=41+3+1<o></o>
=53+2+2<o></o>
<o> </o>
This is so they “can see what each quarters increase was as well as the starting price in January”
<o> </o>
(OK here is where I really started scratching my head because they KNOW they will have to click on each cell individually to see all 3 numbers and they know you can only select 1 cell at a time, so instead of listing the increases in separate columns so you can see the Start price, 1<SUP>st</SUP> increase, 2<SUP>nd</SUP> increase and the final total all at once….. they want to click each cell individually???!!!??)
<o> </o>
And what about next quarter?
<o> </o>
They will edit all the formulas and add the 3<SUP>rd</SUP> quarter increase so it will be like this
=42+2+1+2<o></o>
=35+1+1+1<o></o>
=41+3+1+3<o></o>
=53+2+2+2<o></o>
<o> </o>
No kidding!! That is what they want to do!!!
<o> </o>
<o> </o>
“What I told them”
I told them they can edit each formula, select the cell reference and hit F9.
This will resolve the value of C2 but keep the “+2+1”
But, they need to edit each formula individually
Of course that was too much work for them and needed a way to do it for the entire product line... (about 300 rows)

If VBA is the only way to do this I would greatly appreciate the code to do this, but whenever I write code for people it always causes issues for ME… usually at 4:55PM on a Friday. Yes, I need to write better code but I prefer to stay away from it whenever possible. If not possible with a formula or other feature, I will take code.
<o> </o>
Thanks all in advance,
<o> </o>
<o>Regards</o>
<o>Matthew</o>
<o> </o>

 

[Juan Pablo González]

Give this a try. I assumed the formulas are in column D *and* the code is only evaluating the first reference... not very smart code.

Option Explicit

Sub Test()
    Dim rng As Range
    Dim cll As Range
    
    Set rng = Range("D2", Cells(Rows.Count, 4).End(xlUp))
    
    For Each cll In rng
        cll.formula = EvaluateReferences(cll.formula)
    Next cll
    
End Sub

Function EvaluateReferences(ByVal formula As String) As String
    Dim v As Variant
    
    v = Split(formula, "+")
    
    v(0) = "=" & Evaluate(v(0))
    
    EvaluateReferences = Join(v, "+")
End Function

 

[mateo]

Juan Pablo!

Caramba! Eso fue rapido Senor!! Opps.. sorry.. this is an English only post.
Wow.. thanks for the quick reply and DEAD ON answer.

this code worked and I already replied and setup a session for tomorrow.. thanks and if you are still in Bogota keep on the lookout for a Sandra Patricia Valencia Arias! Almost married that girl while I was in the Navy... LOL...

thanks again.
mil gracias

 

[Juan Pablo González]

Juan Pablo!

Caramba! Eso fue rapido Senor!! Opps.. sorry.. this is an English only post.
Wow.. thanks for the quick reply and DEAD ON answer.

this code worked and I already replied and setup a session for tomorrow.. thanks and if you are still in Bogota keep on the lookout for a Sandra Patricia Valencia Arias! Almost married that girl while I was in the Navy... LOL...

thanks again.
mil gracias

LOL, thanks... I'm still in Bogotá, but I don't think my wife would appreciate much me being on the lookout for her.