| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Total a Variable
Hi,
This Snippet of Code Works Perfect and Outputs the Results to a Worksheet :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i My Question is, How can I Modify the Above Code so that it Produces the Total of the Values of nType(i) at the Bottom Please. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
For i = 21 To 279 With Cells(i+280-21,ActiveCell.Column) .Offset(0, 0).Value = "Result" .Offset(0, 1).Value = i .Offset(0, 2).Value = nType(i) End With Next i -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Hi, This Snippet of Code Works Perfect and Outputs the Results to a Worksheet :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i My Question is, How can I Modify the Above Code so that it Produces the Total of the Values of nType(i) at the Bottom Please. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Another interpretation:
For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R[-259]C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi, This Snippet of Code Works Perfect and Outputs the Results to a Worksheet :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i My Question is, How can I Modify the Above Code so that it Produces the Total of the Values of nType(i) at the Bottom Please. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Thanks Bob & Tom for the Replies,
I think my Biggest Problem is the Fact that "i" can be Smaller OR Larger. I know that this Works :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i ActiveCell.Offset(0, 2).Value = Application.Sum(Range("D4:D262")) But I would have to Continuously Change the Range Accordingly. I Tried your Solution Tom and it Worked Great. I Tried Bobs Solution But could Not get it to Work. All the Best. Paul Total a Variable From: Tom Ogilvy Another interpretation: For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R[-259]C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi, This Snippet of Code Works Perfect and Outputs the Results to a Worksheet :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i My Question is, How can I Modify the Above Code so that it Produces the Total of the Values of nType(i) at the Bottom Please. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
j = activeCell.row
For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R" & j & "C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Thanks Bob & Tom for the Replies, I think my Biggest Problem is the Fact that "i" can be Smaller OR Larger. I know that this Works :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i ActiveCell.Offset(0, 2).Value = Application.Sum(Range("D4:D262")) But I would have to Continuously Change the Range Accordingly. I Tried your Solution Tom and it Worked Great. I Tried Bobs Solution But could Not get it to Work. All the Best. Paul Total a Variable From: Tom Ogilvy Another interpretation: For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R[-259]C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi, This Snippet of Code Works Perfect and Outputs the Results to a Worksheet :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i My Question is, How can I Modify the Above Code so that it Produces the Total of the Values of nType(i) at the Bottom Please. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Brilliant Tom,
Thanks Very Much. Could you Just Explain Please what this Actually does :- rng.formulaR1C1 = "=Sum(R" & j & "C:R[-1]C)" All the Best. Paul Total a Variable From: Tom Ogilvy j = activeCell.row For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R" & j & "C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Thanks Bob & Tom for the Replies, I think my Biggest Problem is the Fact that "i" can be Smaller OR Larger. I know that this Works :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i ActiveCell.Offset(0, 2).Value = Application.Sum(Range("D4:D262")) But I would have to Continuously Change the Range Accordingly. I Tried your Solution Tom and it Worked Great. I Tried Bobs Solution But could Not get it to Work. All the Best. Paul Total a Variable From: Tom Ogilvy Another interpretation: For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R[-259]C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi, This Snippet of Code Works Perfect and Outputs the Results to a Worksheet :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i My Question is, How can I Modify the Above Code so that it Produces the Total of the Values of nType(i) at the Bottom Please. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
It creates a SUM formula in the cell defined in the rng variable. The SUM
range starts at the and row of the initial activecell cell and the column of the current active cell (R" & j & "C, as j is set to Activecell.Row up-front), and ends at the previous row of the current activecell and the column of the current activecell (R[-1]C). This formula gets set to a value in the next line rng.Formula = rng.Value. -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Brilliant Tom, Thanks Very Much. Could you Just Explain Please what this Actually does :- rng.formulaR1C1 = "=Sum(R" & j & "C:R[-1]C)" All the Best. Paul Total a Variable From: Tom Ogilvy j = activeCell.row For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R" & j & "C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Thanks Bob & Tom for the Replies, I think my Biggest Problem is the Fact that "i" can be Smaller OR Larger. I know that this Works :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i ActiveCell.Offset(0, 2).Value = Application.Sum(Range("D4:D262")) But I would have to Continuously Change the Range Accordingly. I Tried your Solution Tom and it Worked Great. I Tried Bobs Solution But could Not get it to Work. All the Best. Paul Total a Variable From: Tom Ogilvy Another interpretation: For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i set rng = ActiveCell.Offset(0,2) rng.formulaR1C1 = "=Sum(R[-259]C:R[-1]C)" rng.Formula = rng.Value -- Regards, Tom Ogilvy "Paul Black" wrote in message ... Hi, This Snippet of Code Works Perfect and Outputs the Results to a Worksheet :- For i = 21 To 279 ActiveCell.Offset(0, 0).Value = "Result" ActiveCell.Offset(0, 1).Value = i ActiveCell.Offset(0, 2).Value = nType(i) ActiveCell.Offset(1, 0).Select Next i My Question is, How can I Modify the Above Code so that it Produces the Total of the Values of nType(i) at the Bottom Please. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate Running Total Grand Total In Pivot Table | Excel Discussion (Misc queries) | |||
| Excel 2002 : Any single button to get sub total and grand total ? | Excel Discussion (Misc queries) | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
| Adding Data Using Multiple Worksheets to Total into a Grand Total | Excel Worksheet Functions | |||
| variable counter/total | Excel Programming | |||
![Paul Black[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
