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

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

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

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

--

Dave Peterson

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

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" wrote in message
...
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

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

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.


"Dave Peterson" schreef in bericht
...
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

--

Dave Peterson

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

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.

Jack Sons wrote:

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.

"Dave Peterson" schreef in bericht
...
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

--

Dave Peterson

--

Dave Peterson

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

Thanks Dave, I understand.

Jack.

"Dave Peterson" schreef in bericht
...
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.

Jack Sons wrote:

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.

"Dave Peterson" schreef in bericht
...
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

--

Dave Peterson

--

Dave Peterson