formula sum in last known column

[288enzo]

This is trickier than I thought.

In this example cell H3 would be =sum(B3:G3), but I don't always know what the last column will be.

Copy of Area 84 IND Sept-October.xlsm
	B	C	D	E	F	G	H
1
2	600336A3	600336PL	600336SSIFU	600336SSIVD	600336VIQS	601036F	Total
3	8	5	7	3	0	6
4	1	1	0	1	0	0
5	2	2	0	1	0	0
6	4	2	0	0	0	0
7	1	2	0	1	0	1
8	1	3	0	0	0	0
9	1	0	0	1	1	1
10	0	4	3	1	1	2
11	0	2	0	0	0	0
12	0	0	6	0	0	1
13	0	0	1	0	2	1
14	0	0	0	1	0	1
15	0	0	0	2	0	0
report

Cell Formulas
Range	Formula
B3:G15	B3	=COUNTIFS(data!$H:$H,$A3,data!$A:$A,B$2)

Named Ranges
Name	Refers To	Cells
data!_FilterDatabase	=data!$A$3:$AI$3	B3:G15

I tried the following, I think it's close.

    Dim Lcol As Long
    Dim rng As Range
    Dim rng2 As Range
   
    Lcol = Cells(2, Columns.Count).End(xlToLeft).Column
   
    Set rng = Range("A2")
    Set rng2 = rng.Offset(1, Lcol - 1)

    rng.Offset(0, Lcol).Value = "Total"
    rng.Offset(1, Lcol).Value = "=sum(B3:" & rng2 & ")"

If I simply use rng2.select G3 is selected. I think something with my formula "=sum(B3:" & rng2 & ")" isn't right.

 

[Fluff]

How about

    rng.Offset(1, Lcol).FormulaR1C1 = "=sum(rc2:rc[-1])"

 

[288enzo]

How about

    rng.Offset(1, Lcol).FormulaR1C1 = "=sum(rc2:rc[-1])"

I tried

    Dim rng As Range
    Set rng = Range("A2")
    rng.Offset(1, Lcol).FormulaR1C1 = "=sum(rc2:rc[-1])"

What it's doing is selecting columns B:G

Hold please, I ran it again and now it's giving me a circular reference. I'm investigating.

 

[288enzo]

How about

    rng.Offset(1, Lcol).FormulaR1C1 = "=sum(rc2:rc[-1])"

Thanks Fluff, one column off but I figured it out.

    Dim Lcol As Long
    Dim rng As Range

    Lcol = Cells(2, Columns.Count).End(xlToLeft).Column
   
    Set rng = Range("A2")
    rng.Offset(1, Lcol - 1).FormulaR1C1 = "=sum(rc2:rc[-1])"

Using FormulaR1C1 is a first for me.

 

[Fluff]

You're welcome & thanks for the feedback.