I am using this to insert a column:
myRng.Offset(0, Res + 1).EntireColumn.Insert
How can I make cell (<NewCol>33) have the value
"=SUM(<newcol>2:<newcol>32)" ?
ie: if the new column is Column C, I would like cell C33 to be
"=SUM(C2:C32)"
Thanks!
I am using this to insert a column:
myRng.Offset(0, Res + 1).EntireColumn.Insert
How can I make cell (<NewCol>33) have the value
"=SUM(<newcol>2:<newcol>32)" ?
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 (<NewCol>33) have the value
'"=SUM(<newcol>2:<newcol>32)" ?
'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
<[email protected]> wrote in message news:[email protected]...
>I am using this to insert a column:
>
> myRng.Offset(0, Res + 1).EntireColumn.Insert
>
> How can I make cell (<NewCol>33) have the value
> "=SUM(<newcol>2:<newcol>32)" ?
>
> 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(<Col>1<>"",<Col>1&"
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
<[email protected]> wrote in message
news:[email protected]...
>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(<Col>1<>"",<Col>1&"
> 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.
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks