Concatenating Formulas
 

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

Concatenating Formulas
 

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" wrote:

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

Concatenating Formulas
 

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

"Joel" wrote:

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" wrote:

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

Concatenating Formulas
 

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.

"Bob Zimski" wrote in message
...
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