I am using this to insert a column:

myRng.Offset(0, Res + 1).EntireColumn.Insert

How can I make cell (<NewCol33) have the value
"=SUM(<newcol2:<newcol32)" ?

ie: if the new column is Column C, I would like cell C33 to be
"=SUM(C2:C32)"

Thanks!

Keyser,

See the macro below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim Res As Integer
Dim myRng As Range

Set myRng = Range("A1")
Res = 1
myRng.Offset(0, Res + 1).EntireColumn.Insert

'How can I make cell (<NewCol33) have the value
'"=SUM(<newcol2:<newcol32)" ?
'ie: if the new column is Column C, I would like cell C33 to be
'"=SUM(C2:C32)"

Cells(33, myRng.Offset(0, Res + 1).Column).FormulaR1C1 = _
"=SUM(R[-31]C:R[-1]C)"

End Sub



wrote in message oups.com...
I am using this to insert a column:

myRng.Offset(0, Res + 1).EntireColumn.Insert

How can I make cell (<NewCol33) have the value
"=SUM(<newcol2:<newcol32)" ?

ie: if the new column is Column C, I would like cell C33 to be
"=SUM(C2:C32)"

Thanks!

Great!

I condensed it down to:

myRng(33, 2).FormulaR1C1 = "=SUM(R[-31]c:r[-1]c)"

Now I would like to add the formula : =IF(<Col1<"",<Col1&"
comments","") is an earlier column in the sheet.
I tried:


myRng(1, Res + 2).FormulaR1C1 = "=IF(C[-Res]<"""",C[-Res]&""
comments"","""") "

but this bombs. Can you tell me what the correct format of the formula
should be in this macro?

Thanks.

keyser,

ActiveCell.FormulaR1C1 = _
"=IF(RC[" & -res & "]<"""",RC[" & -res & "]&"" comments"","""")"

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
I condensed it down to:

myRng(33, 2).FormulaR1C1 = "=SUM(R[-31]c:r[-1]c)"

Now I would like to add the formula : =IF(<Col1<"",<Col1&"
comments","") is an earlier column in the sheet.
I tried:


myRng(1, Res + 2).FormulaR1C1 = "=IF(C[-Res]<"""",C[-Res]&""
comments"","""") "

but this bombs. Can you tell me what the correct format of the formula
should be in this macro?

Thanks.