ActiveCell.FormulaR1C1 = "=SUM(R[-tellerteller]C:R[-1]C)"

[Jack Sons]

Hi all,

I have thes lines of code:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2, "Q")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-tellerteller]C:R[-1]C)"

In this case at that stage tellerteller = 9
So I want in Q11 the sum in column Q from Q2 (Q1 is the header) up to Q10,
this gives an error.

1. What is wrong and how should the code be?
2. Is there no sum function in VBA, I mean sum over a range?

Jack Sons
The Netherlands

 

[Dave Peterson]

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2, "Q")).formular1c1 _
= "=SUM(R[-" & tellerteller & "]C:R[-1]C)"

No need to select the range first. Just plop that .formular1c1 into the range.

If you always wanted to start in row 2, you could even use:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2, "Q")).formular1c1 _
= "=SUM(R2C:R[-1]C)"

========
In VBA, you could either loop through a range and check for numbers, then add:

dim myCell as range
dim myRng as range
dim myTotal as double
set myrng = somerangehere
For each mycell in myrng.cells
if isnumeric(mycell.value) then
mytotal = mytotal + mysum.value
end if
next mycell


or just ask excel for some help:
mytotal = application.sum(myrng)

Hi all,

I have thes lines of code:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2, "Q")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-tellerteller]C:R[-1]C)"

In this case at that stage tellerteller = 9
So I want in Q11 the sum in column Q from Q2 (Q1 is the header) up to Q10,
this gives an error.

1. What is wrong and how should the code be?
2. Is there no sum function in VBA, I mean sum over a range?

Jack Sons
The Netherlands

 

[Charles Williams]

Try:
Range("Q" & cstr(tellerteller+2)).formular1c1="=SUM(R[-" & tellerteller &
"]C:R[-1]C)"


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Jack Sons]

Thanks again Dave.

Every now and then, at unexpected points, I see that in worksheet functions
as well as in VBA things like -abc or KK* will not work and should be
"-"&"abc" or "KK"&"*" or perhaps "KK"&*.

Very confusing (to me, and maybe also to others).
Please shed some light on this, do you know of a kind of rule of thumb to
make this more clear?

Jack.

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).formular1c1 _
= "=SUM(R[-" & tellerteller & "]C:R[-1]C)"

No need to select the range first. Just plop that .formular1c1 into the
range.

If you always wanted to start in row 2, you could even use:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).formular1c1 _
= "=SUM(R2C:R[-1]C)"

========
In VBA, you could either loop through a range and check for numbers, then
add:

dim myCell as range
dim myRng as range
dim myTotal as double
set myrng = somerangehere
For each mycell in myrng.cells
if isnumeric(mycell.value) then
mytotal = mytotal + mysum.value
end if
next mycell


or just ask excel for some help:
mytotal = application.sum(myrng)

Hi all,

I have thes lines of code:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-tellerteller]C:R[-1]C)"

In this case at that stage tellerteller = 9
So I want in Q11 the sum in column Q from Q2 (Q1 is the header) up to
Q10,
this gives an error.

1. What is wrong and how should the code be?
2. Is there no sum function in VBA, I mean sum over a range?

Jack Sons
The Netherlands

 

[Dave Peterson]

In your example, the tellerteller is a variable inside your code. It doesn't
mean anything to excel.

So if you did:
somecell.value = "tellerteller"
you'd be telling excel that you want that cell to have a string value of
"tellerteller". It would have nothing to do with that variable.

When you do
somecell.value = tellerteller
(without the quotes)
then excel will fill the cell with the value that the variable is holding.

It really boils down to how strings are combined and what you want/need to do.

somecell.value = "1+2+3"
won't result in a 6 put into that cell. Because you're working with strings.

somecell.value = 1+2+3
will be a 6 because that expression isn't representing a string.

I'm not sure if this explains the difference, though.

Thanks again Dave.

Every now and then, at unexpected points, I see that in worksheet functions
as well as in VBA things like -abc or KK* will not work and should be
"-"&"abc" or "KK"&"*" or perhaps "KK"&*.

Very confusing (to me, and maybe also to others).
Please shed some light on this, do you know of a kind of rule of thumb to
make this more clear?

Jack.

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).formular1c1 _
= "=SUM(R[-" & tellerteller & "]C:R[-1]C)"

No need to select the range first. Just plop that .formular1c1 into the
range.

If you always wanted to start in row 2, you could even use:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).formular1c1 _
= "=SUM(R2C:R[-1]C)"

========
In VBA, you could either loop through a range and check for numbers, then
add:

dim myCell as range
dim myRng as range
dim myTotal as double
set myrng = somerangehere
For each mycell in myrng.cells
if isnumeric(mycell.value) then
mytotal = mytotal + mysum.value
end if
next mycell


or just ask excel for some help:
mytotal = application.sum(myrng)

Hi all,

I have thes lines of code:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-tellerteller]C:R[-1]C)"

In this case at that stage tellerteller = 9
So I want in Q11 the sum in column Q from Q2 (Q1 is the header) up to
Q10,
this gives an error.

1. What is wrong and how should the code be?
2. Is there no sum function in VBA, I mean sum over a range?

Jack Sons
The Netherlands

 

[Jack Sons]

Thanks Dave, I understand.

Jack.

In your example, the tellerteller is a variable inside your code. It
doesn't
mean anything to excel.

So if you did:
somecell.value = "tellerteller"
you'd be telling excel that you want that cell to have a string value of
"tellerteller". It would have nothing to do with that variable.

When you do
somecell.value = tellerteller
(without the quotes)
then excel will fill the cell with the value that the variable is holding.

It really boils down to how strings are combined and what you want/need to
do.

somecell.value = "1+2+3"
won't result in a 6 put into that cell. Because you're working with
strings.

somecell.value = 1+2+3
will be a 6 because that expression isn't representing a string.

I'm not sure if this explains the difference, though.

Thanks again Dave.

Every now and then, at unexpected points, I see that in worksheet
functions
as well as in VBA things like -abc or KK* will not work and should be
"-"&"abc" or "KK"&"*" or perhaps "KK"&*.

Very confusing (to me, and maybe also to others).
Please shed some light on this, do you know of a kind of rule of thumb
to
make this more clear?

Jack.

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).formular1c1 _
= "=SUM(R[-" & tellerteller & "]C:R[-1]C)"

No need to select the range first. Just plop that .formular1c1 into
the
range.

If you always wanted to start in row 2, you could even use:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).formular1c1 _
= "=SUM(R2C:R[-1]C)"

========
In VBA, you could either loop through a range and check for numbers,
then
add:

dim myCell as range
dim myRng as range
dim myTotal as double
set myrng = somerangehere
For each mycell in myrng.cells
if isnumeric(mycell.value) then
mytotal = mytotal + mysum.value
end if
next mycell


or just ask excel for some help:
mytotal = application.sum(myrng)




Jack Sons wrote:

Hi all,

I have thes lines of code:

Range(Cells(tellerteller + 2, "Q"), Cells(tellerteller + 2,
"Q")).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-tellerteller]C:R[-1]C)"

In this case at that stage tellerteller = 9
So I want in Q11 the sum in column Q from Q2 (Q1 is the header) up to
Q10,
this gives an error.

1. What is wrong and how should the code be?
2. Is there no sum function in VBA, I mean sum over a range?

Jack Sons
The Netherlands