From: Jack Sons on
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



From: Dave Peterson on
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
From: Charles Williams on
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" <j.sons(a)planet.nl> wrote in message
news:OwwRClPQKHA.1280(a)TK2MSFTNGP04.phx.gbl...
> 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
>
>
>
>


From: Jack Sons on
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" <petersod(a)verizonXSPAM.net> schreef in bericht
news:4AC1FAF8.F2D88858(a)verizonXSPAM.net...
> 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


From: Dave Peterson on
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" <petersod(a)verizonXSPAM.net> schreef in bericht
> news:4AC1FAF8.F2D88858(a)verizonXSPAM.net...
> > 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