Add formula to cell to sum range of cells
 

I am trying to write a macro that selects a range of cells and then format a
nearby cell to show the sum of that range. The code i have does so by
selecting the range, naming it, and using the name in the formula. The
problem is that I want to run this for multiple sheets in the workbook. I
can't figure out how to use a variable to name the range and use it in the
sum formula. Using ActiveSheet.name + "Days" in the sum formula creates an
error.

Range("B11").Select
Range(Selection, Selection.End(xlDown).Offset(-1#)).Select

ActiveWorkbook.Names.Add name:=ActiveSheet.name + "Days", _
RefersToR1C1:=Selection

Range("F10").NumberFormat = "0.00%"
Range("F10").FormulaR1C1 = "=Sum(ActiveSheet.name + "Days")"

Add formula to cell to sum range of cells
 

Not sure what you are looking for, but this will assign a variable to the
range and then name it.

Set sh = Sheets(ActiveSheet.Name)
With sh
Set myVar = Range("B11", Range("B11").End(xlDown).Offset(-1, 0))
ActiveWorkbook.Names.Add Name:=ActiveSheet.Name + "Days", _
RefersToR1C1:=myVar.Address
Range("F10").NumberFormat = "0.00%"
Range("F10") = WorksheetFunction.Sum(myVar)
End With



"kevlarmcc" wrote in message
...
I am trying to write a macro that selects a range of cells and then format
a
nearby cell to show the sum of that range. The code i have does so by
selecting the range, naming it, and using the name in the formula. The
problem is that I want to run this for multiple sheets in the workbook. I
can't figure out how to use a variable to name the range and use it in the
sum formula. Using ActiveSheet.name + "Days" in the sum formula creates an
error.

Range("B11").Select
Range(Selection, Selection.End(xlDown).Offset(-1#)).Select

ActiveWorkbook.Names.Add name:=ActiveSheet.name + "Days", _
RefersToR1C1:=Selection

Range("F10").NumberFormat = "0.00%"
Range("F10").FormulaR1C1 = "=Sum(ActiveSheet.name + "Days")"

Add formula to cell to sum range of cells
 

hi
use a variable.
Range("B11").Select
Range(Selection, Selection.End(xlDown).Offset(-1#)).Select
Dim strng As String '**********
strng = ActiveSheet.Name & "Days"

ActiveWorkbook.Names.Add Name:=strng, _
RefersToR1C1:=Selection

Range("F10").NumberFormat = "0.00%"
Range("F10").FormulaR1C1 = "=Sum(" & strng & ")"

regards
FSt1


"kevlarmcc" wrote:

I am trying to write a macro that selects a range of cells and then format a
nearby cell to show the sum of that range. The code i have does so by
selecting the range, naming it, and using the name in the formula. The
problem is that I want to run this for multiple sheets in the workbook. I
can't figure out how to use a variable to name the range and use it in the
sum formula. Using ActiveSheet.name + "Days" in the sum formula creates an
error.

Range("B11").Select
Range(Selection, Selection.End(xlDown).Offset(-1#)).Select

ActiveWorkbook.Names.Add name:=ActiveSheet.name + "Days", _
RefersToR1C1:=Selection

Range("F10").NumberFormat = "0.00%"
Range("F10").FormulaR1C1 = "=Sum(ActiveSheet.name + "Days")"