Total a Variable

[Paul Black]

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!

 

[Bob Phillips]

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)

 

[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

 

[Paul Black]

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("D4262"))

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



Re: 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

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!

 

[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

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("D4262"))

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



Re: 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

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!

 

[Paul Black]

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



Re: 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

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("D4262"))

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



Re: 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

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!

 

[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)

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



Re: 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

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("D4262"))

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



Re: 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

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!

 

[Paul Black]

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



Re: 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)

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



Re: 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

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("D4262"))

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



Re: 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

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!

 

[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)

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



Re: 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)

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



Re: 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

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("D4262"))

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



Re: 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


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!

 

[Paul Black]

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("D4262"))

… 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(D4262" & 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



Re: 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)

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



Re: 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)

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



Re: 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

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("D4262"))

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



Re: 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


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!

 

[Bob Phillips]

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)

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("D4262"))

. 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(D4262" & 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



Re: 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)

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



Re: 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)

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



Re: 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


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("D4262"))

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



Re: 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


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!

 

[Dana DeLouis]

Just curious, that's all. What does you function "nType( )" do on the
sequence of integers 21-279?

--
Dana DeLouis
Win XP & Office 2003

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>

 

[Paul Black]

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" & 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!

 

[Dana DeLouis]

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

 

[Bob Phillips]

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]

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!

 

[Paul Black]

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!

 

[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]

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



Re: 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)

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!

 

[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)

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



Re: 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)

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!