Sum Dynamic Range - VBA

[cfatz1]

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

 

[Dick Kusleika]

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.

 

[Guest]

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"

 

[cfatz1]

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

 

[Guest]

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.

 

[Guest]

One more thought why not just use =sum(F:F)

Arun

 

[Guest]

Oops, Sorry it should be =sum(F4:F65536)

 

[cfatz1]

Arun,

Thanks for the tips! I finally got it to work properly. I appreciate
the help each of you gave me with this....

Chris