Concatenating Formulas

[Bob Zimski]

My code below does exactly what I want for the first iteration, but the
second iteration doesn't yield what I want. Below shows what I would like the
result to be. The loop goes through the base column and adds pairs together.
However, I don't just want to add the pairs of numbers together, I want to
build the forumla up as what I show in the Formula Bar section. I am guessing
this has a lot more code behind it than what I have. I suspect that I have to
check if any or both cells have numbers only first and then create a formula
as I have done below. If there is a number and a formula then I would have to
build a different string, and finally if they are both formula's I would have
to replace the = with a + between the pairs.

Am I over complicating this?
The next question is, how do I pickup the formula, add to it with changes
and plunk it back in VB code.

Thanks

Bob


Base Result Formula Bar
2 9 =2+3+4
3 7 =3+4
4


Dim r As Integer
For r = 4 To 3 Step -1
Range("B" & r - 1).Formula = "=" & Range("A" & r - 1) & "+" & Range("A" & r)
Next r

 

[Joel]

I would do the code below. Using SUM and placing the dollar sign gets the
same reults as you method

LastRow = 20
Range("B2").formula = "=SUM(A2:A$" & LastRow & ")"
Range("B2").copy _
Destination:=Range("B2:B" & LastRow)

 

[Bob Zimski]

Thanks a nifty trick and someday I'm sure to use it. In this case I need the
results to be exactly as I portrayed as I need to retain =2+3+4 because I am
consolidating lines and I need to see the makeup of the components once I
consolidate the lines.

Bob

 

[JLGWhiz]

Bob, what you are doing when you try to run the macro a second time is
simply putting the same formula in the same two cells by overwriting the
original.

You might want to try doing the math in code and then adding it to the cell
value by concatenation.

For R = 4 To 3 Step -1
Range("B" & r-1) = Range("B" & r-1).Value & "+" & _
Range("A" & r - 1) & "+" & Range("A" & r).Value
Next

This puts the numbers in as text and not a formula.