| 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! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Thanks Bob for the Explanation, I can Understand how this Works Now. I
will Probably Use this Quite Often in the Future. I will look into the R1C1 Notation More. It is so Much Easier than Setting a Fixed Range and then having to Physically Change it as the Range Changes. Thanks Again. All the Best. Paul Total a Variable From: Bob Phillips 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! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
You can do just as well non-R1C1
rng.formula = "=Sum(A1:A" & j & ")" but getting the column tends to be more problemmatical -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Thanks Bob for the Explanation, I can Understand how this Works Now. I will Probably Use this Quite Often in the Future. I will look into the R1C1 Notation More. It is so Much Easier than Setting a Fixed Range and then having to Physically Change it as the Range Changes. Thanks Again. All the Best. Paul Total a Variable From: Bob Phillips 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! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Thanks for the Follow up Reply Bob.
Using … Dim rng As Range Dim j As Integer 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 … Works Great Because it does Not Matter if there are Values Above OR Below the Values Output from the Program. I did Try … ActiveCell.Offset(0, 2).Value = Application.Sum(Range("D4:D262")) … which Worked as Well, and did Not Need the rng & i Included, But I would have to Continuously Change the Range Accordingly.. Your … rng.Formula = "=Sum(D4:D262" & j & ")" … is Similar to Mine with the Exception of Using the rng & i. But this would Also Mean having to Continuously Change the Range Accordingly.. What is the Difference Between My One and Yours. Which One of the Above would you Recommend Using for Flexibility and a Changeable Range Please. All the Best. Paul Total a Variable From: Bob Phillips You can do just as well non-R1C1 rng.formula = "=Sum(A1:A" & j & ")" but getting the column tends to be more problemmatical -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Thanks Bob for the Explanation, I can Understand how this Works Now. I will Probably Use this Quite Often in the Future. I will look into the R1C1 Notation More. It is so Much Easier than Setting a Fixed Range and then having to Physically Change it as the Range Changes. Thanks Again. All the Best. Paul Total a Variable From: Bob Phillips 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! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Paul,
I have to be honest, I would (do) 99% use the R1C1 method, it is the most versatile and flexible, I was just making the point that there are other ways which suit certain circumstances, and let's be honest "SUM(A1:A" & j & ")" is clearer than "=SUM(R1C:R" & j & "C)" -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Thanks for the Follow up Reply Bob. Using . Dim rng As Range Dim j As Integer 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 . Works Great Because it does Not Matter if there are Values Above OR Below the Values Output from the Program. I did Try . ActiveCell.Offset(0, 2).Value = Application.Sum(Range("D4:D262")) . which Worked as Well, and did Not Need the rng & i Included, But I would have to Continuously Change the Range Accordingly.. Your . rng.Formula = "=Sum(D4:D262" & j & ")" . is Similar to Mine with the Exception of Using the rng & i. But this would Also Mean having to Continuously Change the Range Accordingly.. What is the Difference Between My One and Yours. Which One of the Above would you Recommend Using for Flexibility and a Changeable Range Please. All the Best. Paul Total a Variable From: Bob Phillips You can do just as well non-R1C1 rng.formula = "=Sum(A1:A" & j & ")" but getting the column tends to be more problemmatical -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Thanks Bob for the Explanation, I can Understand how this Works Now. I will Probably Use this Quite Often in the Future. I will look into the R1C1 Notation More. It is so Much Easier than Setting a Fixed Range and then having to Physically Change it as the Range Changes. Thanks Again. All the Best. Paul Total a Variable From: Bob Phillips 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! |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Just curious, that's all. What does you function "nType( )" do on the
sequence of integers 21-279? -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message ... Thanks for the Follow up Reply Bob. Using . Dim rng As Range Dim j As Integer 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 < snip |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Bob,
Thanks Again. I Tried Using your Suggestion But could Not get it to Work Properly. 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.Formula = "=Sum(D1:D" & j & ")" rng.Formula = rng.Value Dana, Function "nType( )" Counts the Number of Values that Match Each Number from 21 to 279. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
I can never get the joining of strings to work correctly either. Therefore,
I let Excel do the hard work. Here's just another option that I use: Sub Demo() Dim H, L, n Const Fx As String = "=SUM(R[-#]C:R[-1]C)" L = 21 H = 279 With ActiveCell For n = L To H .Offset(n - L, 0).Resize(1, 3) = Array("Result", n, ntype(n)) Next n .Offset(H - L + 1, 2) = Replace(Fx, "#", H - L + 1) End With End Sub HTH -- Dana DeLouis Win XP & Office 2003 "Paul Black" wrote in message ... Bob, Thanks Again. I Tried Using your Suggestion But could Not get it to Work Properly. 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.Formula = "=Sum(D1:D" & j & ")" rng.Formula = rng.Value Dana, Function "nType( )" Counts the Number of Values that Match Each Number from 21 to 279. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Sorry Paul, that wasn't an explanation in this case, it was just an
example. In this case j is up at the head of the range. -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Bob, Thanks Again. I Tried Using your Suggestion But could Not get it to Work Properly. 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.Formula = "=Sum(D1:D" & j & ")" rng.Formula = rng.Value Dana, Function "nType( )" Counts the Number of Values that Match Each Number from 21 to 279. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Thanks to Everyone for the Replies and Help.
I Played Around with the R1C1 Notation to try and Understand it a Bit Better. The Only thing I have to be Careful of is that there are NO Values Above the Output, Otherwise these Values will get Included in the Total, But I Still think this is a Better Way of Doing it Instead of Hard Coding the Cell References. Thanks Again Everyone. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Just a Follow up Question Please.
After the Program has Produced ALL the Results, How would I get it to give me the Sum Total of nType Based on the i Variable. What I Mean is, if I Wanted to Know the Sum Total of the Values of nType when the Values Produced for i are Say 50 and < 100 for Example. This could go in a Cell Beneath the Information Already Produced. 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 Thanks Very Much in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Untried, but here is a shot
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 Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R50C:R100)" rng.Formula = rng.Value if you want variables say k & l, then Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R" & k & "C:R" & l & ")" rng.Formula = rng.Value -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Just a Follow up Question Please. After the Program has Produced ALL the Results, How would I get it to give me the Sum Total of nType Based on the i Variable. What I Mean is, if I Wanted to Know the Sum Total of the Values of nType when the Values Produced for i are Say 50 and < 100 for Example. This could go in a Cell Beneath the Information Already Produced. 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 Thanks Very Much in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Thanks Bob,
That is Not Quite what I Wanted. What I would like is Something like this :- If i = 23 And i <= 50 Then Sum Total nType which are to the Right of the i Values. For Example, I would like the Results of the Below Table in Addition to the Results Already Produced by the Program. i = 23 And i <= 50 = nType Sum i = 51 And i <= 100 = nType Sum i = 101 And i <= 150 = nType Sum i = 151 And i <= 200 = nType Sum i = 201 And i <= 250 = nType Sum i = 250 And i <= 279 = nType Sum I would Preferably like it Without Hard Coded Formulas Please. I wish I was on Poole Quay Now having a Nice Cold Pint. Thanks Bob. All the Best. Paul Total a Variable From: Bob Phillips Untried, but here is a shot 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 Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R50C:R100)" rng.Formula = rng.Value if you want variables say k & l, then Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R" & k & "C:R" & l & ")" rng.Formula = rng.Value -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Just a Follow up Question Please. After the Program has Produced ALL the Results, How would I get it to give me the Sum Total of nType Based on the i Variable. What I Mean is, if I Wanted to Know the Sum Total of the Values of nType when the Values Produced for i are Say 50 and < 100 for Example. This could go in a Cell Beneath the Information Already Produced. 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 Thanks Very Much 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! |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Paul,
What exactly do you mean by i here, as in the code it is just a loop counter. By the time you get to the end, i is always 279. Perhaps you mean that you want totals 23-50 in row 50, 51-100 in 100, in the adjacent column (Offset(0,3))? -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Thanks Bob, That is Not Quite what I Wanted. What I would like is Something like this :- If i = 23 And i <= 50 Then Sum Total nType which are to the Right of the i Values. For Example, I would like the Results of the Below Table in Addition to the Results Already Produced by the Program. i = 23 And i <= 50 = nType Sum i = 51 And i <= 100 = nType Sum i = 101 And i <= 150 = nType Sum i = 151 And i <= 200 = nType Sum i = 201 And i <= 250 = nType Sum i = 250 And i <= 279 = nType Sum I would Preferably like it Without Hard Coded Formulas Please. I wish I was on Poole Quay Now having a Nice Cold Pint. Thanks Bob. All the Best. Paul Total a Variable From: Bob Phillips Untried, but here is a shot 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 Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R50C:R100)" rng.Formula = rng.Value if you want variables say k & l, then Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R" & k & "C:R" & l & ")" rng.Formula = rng.Value -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Just a Follow up Question Please. After the Program has Produced ALL the Results, How would I get it to give me the Sum Total of nType Based on the i Variable. What I Mean is, if I Wanted to Know the Sum Total of the Values of nType when the Values Produced for i are Say 50 and < 100 for Example. This could go in a Cell Beneath the Information Already Produced. 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 Thanks Very Much 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! |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Hi Bob,
It was my Fault, I did Not Explain it as well as I could have. I will give it Another go. You are Right, i is Just a Loop Counter that Produces the Numbers from 21 to 279 in a Column. The nType(i) Produces the Results Associated with the i Values. If for Arguments Sake the i Numbers ( from 21 to 279 ) are Output in Cells B4:B260, the Program Outputs the nType Totals Associated with these i Numbers in Cells C4:C260. What I would like is the Sum Total when :- If i = 23 And If i <= 50 Then nType Total Goes in Column C Under the Other Totals If i = 51 And If i <= 100 Then nType Total Goes in Column C Under the Other Totals If i = 101 And If i <= 150 Then nType Total Goes in Column C Under the Other Totals If i = 151 And If i <= 200 Then nType Total Goes in Column C Under the Other Totals If i = 201 And If i <= 250 Then nType Total Goes in Column C Under the Other Totals If i = 250 And If i <= 279 Then nType Total Goes in Column C Under the Other Totals These Results will be Underneath the Results that the Program Already Produces. I would Preferably like it Without Hard Coded Formulas Please. Thanks Bob. All the Best. Paul Total a Variable From: Bob Phillips Paul, What exactly do you mean by i here, as in the code it is just a loop counter. By the time you get to the end, i is always 279. Perhaps you mean that you want totals 23-50 in row 50, 51-100 in 100, in the adjacent column (Offset(0,3))? -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Thanks Bob, That is Not Quite what I Wanted. What I would like is Something like this :- If i = 23 And i <= 50 Then Sum Total nType which are to the Right of the i Values. For Example, I would like the Results of the Below Table in Addition to the Results Already Produced by the Program. i = 23 And i <= 50 = nType Sum i = 51 And i <= 100 = nType Sum i = 101 And i <= 150 = nType Sum i = 151 And i <= 200 = nType Sum i = 201 And i <= 250 = nType Sum i = 250 And i <= 279 = nType Sum I would Preferably like it Without Hard Coded Formulas Please. I wish I was on Poole Quay Now having a Nice Cold Pint. Thanks Bob. All the Best. Paul Total a Variable From: Bob Phillips Untried, but here is a shot 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 Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R50C:R100)" rng.Formula = rng.Value if you want variables say k & l, then Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R" & k & "C:R" & l & ")" rng.Formula = rng.Value -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Just a Follow up Question Please. After the Program has Produced ALL the Results, How would I get it to give me the Sum Total of nType Based on the i Variable. What I Mean is, if I Wanted to Know the Sum Total of the Values of nType when the Values Produced for i are Say 50 and < 100 for Example. This could go in a Cell Beneath the Information Already Produced. 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 Thanks Very Much 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! |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Total a Variable
Hi Bob,
It was my Fault, I did Not Explain it as well as I could have. I will give it Another go. You are Right, i is Just a Loop Counter that Produces the Numbers from 21 to 279 in a Column. The nType(i) Produces the Results Associated with the i Values. If for Arguments Sake the i Numbers ( from 21 to 279 ) are Output in Cells B4:B260, the Program Outputs the nType Totals Associated with these i Numbers in Cells C4:C260. What I would like is the Sum Total when :- If i = 23 And If i <= 50 Then nType Total Goes in Column C Under the Other Totals If i = 51 And If i <= 100 Then nType Total Goes in Column C Under the Other Totals If i = 101 And If i <= 150 Then nType Total Goes in Column C Under the Other Totals If i = 151 And If i <= 200 Then nType Total Goes in Column C Under the Other Totals If i = 201 And If i <= 250 Then nType Total Goes in Column C Under the Other Totals If i = 250 And If i <= 279 Then nType Total Goes in Column C Under the Other Totals These Results will be Underneath the Results that the Program Already Produces. I would Preferably like it Without Hard Coded Formulas Please. Thanks Bob. All the Best. Paul Total a Variable From: Bob Phillips Paul, What exactly do you mean by i here, as in the code it is just a loop counter. By the time you get to the end, i is always 279. Perhaps you mean that you want totals 23-50 in row 50, 51-100 in 100, in the adjacent column (Offset(0,3))? -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Thanks Bob, That is Not Quite what I Wanted. What I would like is Something like this :- If i = 23 And i <= 50 Then Sum Total nType which are to the Right of the i Values. For Example, I would like the Results of the Below Table in Addition to the Results Already Produced by the Program. i = 23 And i <= 50 = nType Sum i = 51 And i <= 100 = nType Sum i = 101 And i <= 150 = nType Sum i = 151 And i <= 200 = nType Sum i = 201 And i <= 250 = nType Sum i = 250 And i <= 279 = nType Sum I would Preferably like it Without Hard Coded Formulas Please. I wish I was on Poole Quay Now having a Nice Cold Pint. Thanks Bob. All the Best. Paul Total a Variable From: Bob Phillips Untried, but here is a shot 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 Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R50C:R100)" rng.Formula = rng.Value if you want variables say k & l, then Set rng = ActiveCell.Offset(1, 2) rng.FormulaR1C1 = "=SUM(R" & k & "C:R" & l & ")" rng.Formula = rng.Value -- HTH RP (remove nothere from the email address if mailing direct) "Paul Black" wrote in message ... Just a Follow up Question Please. After the Program has Produced ALL the Results, How would I get it to give me the Sum Total of nType Based on the i Variable. What I Mean is, if I Wanted to Know the Sum Total of the Values of nType when the Values Produced for i are Say 50 and < 100 for Example. This could go in a Cell Beneath the Information Already Produced. 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 Thanks Very Much 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)
Linear Mode
