Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
Can anyone tell me why the end result of this function gives me the
#NAME? error in cell F2? Function RangeTot() Dim myLastCell As String Dim myFirstCell As String Selection.End(xlDown).Select myFirstCell = ActiveCell.Address Selection.End(xlDown).Select myLastCell = ActiveCell.Address Range("F2").Select ActiveCell.FormulaR1C1 = "=sum(myFirstCell" & ":myLastCell" & ")" End Function TIA, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
Chris
Because you're not including the parentheses, i.e. you're typing =rangetot instead of =rangetot(). Once you put the parens in there, you will get the VALUE error because functions can only return values. They can't Select or change properties like FormulaR1C1. -- Dick Kusleika MVP - Excel Daily Dose of Excel http://www.dicks-blog.com wrote: Can anyone tell me why the end result of this function gives me the #NAME? error in cell F2? Function RangeTot() Dim myLastCell As String Dim myFirstCell As String Selection.End(xlDown).Select myFirstCell = ActiveCell.Address Selection.End(xlDown).Select myLastCell = ActiveCell.Address Range("F2").Select ActiveCell.FormulaR1C1 = "=sum(myFirstCell" & ":myLastCell" & ")" End Function TIA, Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
as written the function would try to overwrie itself with a formula
A function is designed to return something not to do something last line of function should typically be RangeTot = 'yourMathHere Looks to me like you want to use a sub not a function if you simply replave "function" with "sub" your code when called will return a formula for totalling from the then activecell to XLdown this will be recalculated but the range will not be dynamic on recalculation If you post what you are trying to do i might be more help there is a way to sum a dynamic list in XL2003 Help"Excel List" " wrote: Can anyone tell me why the end result of this function gives me the #NAME? error in cell F2? Function RangeTot() Dim myLastCell As String Dim myFirstCell As String Selection.End(xlDown).Select myFirstCell = ActiveCell.Address Selection.End(xlDown).Select myLastCell = ActiveCell.Address Range("F2").Select ActiveCell.FormulaR1C1 = "=sum(myFirstCell" & ":myLastCell" & ")" End Function TIA, Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
Thanks Dick and Vaca.
What I am trying to do is SUM a dynamic range...but I need the total at the top of the page (i.e. - cell F2). The range I want to sum begins in F4 and is dynamic from there. I was trying to save F4 to a variable (variable - myFirstCell)....and then jump to the last cell in the column and save that address to a variable as well (variable - myLastCell). After that....I want to go to cell F2 and add the two variables to get a value. I want the formula in F2 to be =SUM(myFirstCell:myLastCell) Thanks for your help..... Chris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
Chris,
your statement is below ActiveCell.FormulaR1C1 = "=sum(myFirstCell" & ":myLastCell" & ")" which should be ActiveCell.FormulaR1C1 = "=sum(" & myFirstCell & ":" & myLastCell & ")" Excel looks for named ranges myFirstCell and myLastCell. Check this out. " wrote: Thanks Dick and Vaca. What I am trying to do is SUM a dynamic range...but I need the total at the top of the page (i.e. - cell F2). The range I want to sum begins in F4 and is dynamic from there. I was trying to save F4 to a variable (variable - myFirstCell)....and then jump to the last cell in the column and save that address to a variable as well (variable - myLastCell). After that....I want to go to cell F2 and add the two variables to get a value. I want the formula in F2 to be =SUM(myFirstCell:myLastCell) Thanks for your help..... Chris |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
One more thought why not just use =sum(F:F)
Arun |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
Oops, Sorry it should be =sum(F4:F65536)
"Arun" wrote: One more thought why not just use =sum(F:F) Arun |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sum Dynamic Range - VBA
Arun,
Thanks for the tips! I finally got it to work properly. I appreciate the help each of you gave me with this.... Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
copying dynamic range based on cell outside of range | Excel Programming | |||
select dynamic range with dynamic start point | Excel Programming | |||
Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming |