Total a Variable

[Paul Black]

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



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

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!

 

[Paul Black]

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



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

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!