Insert formula in last column macro

[doriannjeshi]

Hi,
I need to insert a formula in the last dynamic column in row 2, of a range that starts at A1.
This is what I have static but I need it dynamic last column:

ActiveCell.FormulaR1C1 = "shuma"
Range("AY2").Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=SUM(RC[-35]:RC[-1])"
Selection.AutoFill Destination:=Range("AY2:AY" & Range("a" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[doriannjeshi]

this is where I need the formula. the end of a range column. Formula to be copied down the last column.

 

[MARK858]

I'll state it again, there is no need to copy anything down (I can't add anymore if you don't get similar results to the below without either an XL2BB of your data or a copy of your workbook so I can see exactly what you have)

With

Book1
	AT	AU	AV	AW	AX	AY
1	Header1	Header2	Header3	Header4	Header5	Header6
2	37	18	13	48	19
3	46	18	35	37	10
4	37	50	33	24	12
5	46	34	40	28	23
6	29	19	24	31	15
7	35	27	49	16	28
8	50	15	19	17	45
9	15	18	11	13	22
10	30	39	29	22	12
11	36	12	24	44	39
Sheet8

The code gives me

Book1
	AT	AU	AV	AW	AX	AY
1	Header1	Header2	Header3	Header4	Header5	Header6
2	37	18	13	48	19	135
3	46	18	35	37	10	146
4	37	50	33	24	12	156
5	46	34	40	28	23	171
6	29	19	24	31	15	118
7	35	27	49	16	28	155
8	50	15	19	17	45	146
9	15	18	11	13	22	79
10	30	39	29	22	12	132
11	36	12	24	44	39	155
Sheet8

With the .Value line commented out (Column BA added manually to show the formulas in Column AY)

Book1
	AT	AU	AV	AW	AX	AY	AZ	BA
1	Header1	Header2	Header3	Header4	Header5	Header6
2	37	18	13	48	19	135		=SUM(P2:AX2)
3	46	18	35	37	10	146		=SUM(P3:AX3)
4	37	50	33	24	12	156		=SUM(P4:AX4)
5	46	34	40	28	23	171		=SUM(P5:AX5)
6	29	19	24	31	15	118		=SUM(P6:AX6)
7	35	27	49	16	28	155		=SUM(P7:AX7)
8	50	15	19	17	45	146		=SUM(P8:AX8)
9	15	18	11	13	22	79		=SUM(P9:AX9)
10	30	39	29	22	12	132		=SUM(P10:AX10)
11	36	12	24	44	39	155		=SUM(P11:AX11)
Sheet8

Cell Formulas
Range	Formula
AY2:AY11	AY2	=SUM(P2:AX2)
BA2:BA11	BA2	=FORMULATEXT(AY2)

 

[doriannjeshi]

The formula is dynamic too, meaning it must sum to that point if possible.
When the range is shorter than the formulas range it returns 0 and defeats the purpose

When using a formula that sums a1:a5 , if the range then being dynamic gets shorter a1:a3 it gives me 0

 

[MARK858]

The formula is dynamic too, meaning it must sum to that point if possible.
When the range is shorter than the formulas range it returns 0 and defeats the purpose

When using a formula that sums a1:a5 , if the range then being dynamic gets shorter a1:a3 it gives me 0

Bar your formula sums across columns not down rows as you have it above, so that makes no sense....

Maybe what you want is to fix the start column in the formula (in the below the start column is always column A)

= "=SUM(RC1:RC[-1])"