ActiveCell.FormulaR1C1 = "=SUM(R[-tellerteller]C:R[-1]C)"
in [General]
|
From: Jack Sons on 29 Sep 2009 07:37 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 29 Sep 2009 08:18 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 29 Sep 2009 08:21 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 29 Sep 2009 16:00 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 29 Sep 2009 16:15 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
|
Next
|
Last
Pages: 1 2 Prev: Entering HH:MM Next: Error Too Many Different Cell Formats (Different than others have |