Prev: Entering HH:MM
Next: Error Too Many Different Cell Formats (Different than others have
From: Jack Sons on 29 Sep 2009 16:58
Thanks Dave, I understand.

Jack.

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


First  |  Prev  | 
Pages: 1 2
Prev: Entering HH:MM
Next: Error Too Many Different Cell Formats (Different than others have