Adding a coulmn total using a range name and sheet name

[burl_rfc_h]

In the code below I created a dynamic range, the range name is based on
the contents in cell 2 of a column (in this case text) and the sheet
name. How can I then place a formula into the 1st cell of the column
that totals up the contents of the dynamic range, using the dynmaic
range name.

Sub Named_Range()

Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"

sName = Cells(2, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_" & s1, _
RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNT(" & s2 & "!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"


Debug.Print sName & "_" & s1, ThisWorkbook.Names( _
sName & "_" & s1).RefersTo

End Sub

Thanks
Burl

 

[Don Guillett]

Does this help?

Sub makerangeandsumattop()
Range(Cells(2, "h"), Cells(5, "h")).Name = "burl"
Cells(1, "H") = Application.Sum(Range("burl"))
'or to leave the formula there
'Cells(1, "H").Formula = "=Sum(burl)"
End Sub

 

[Jim Cone]

Burl,
Am using CountA instead of Count.
Also, using EntireColumn creates a circular reference.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware/

'--------------------------
Sub Named_Range()
Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"

sName = Cells(2, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_" & s1, _
RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, ActiveCell.Column).Address & _
",1,0,COUNTA(" & s2 & "!" & Range(Cells(2, ActiveCell.Column), _
Cells(Rows.Count, ActiveCell.Column)).Address & ")-1,1)"

'This adds the formula...
Cells(1, ActiveCell.Column).Formula = "=Sum(" & sName & "_" & s1 & ")"

Debug.Print sName & "_" & s1, ThisWorkbook.Names( _
sName & "_" & s1).RefersTo
End Sub
'-------------------


In the code below I created a dynamic range, the range name is based on
the contents in cell 2 of a column (in this case text) and the sheet
name. How can I then place a formula into the 1st cell of the column
that totals up the contents of the dynamic range, using the dynmaic
range name.

Sub Named_Range()
Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"
sName = Cells(2, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_" & s1, _
RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNT(" & s2 & "!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"
Debug.Print sName & "_" & s1, ThisWorkbook.Names( _
sName & "_" & s1).RefersTo
End Sub
Thanks
Burl