Total a Variable Based on Another Variable Please

  • Thread starter Thread starter Paul Black
  • Start date Start date
P

Paul Black

Hi,

The Snippet of Code Below Works Great.
i is a Loop Counter that Produces the Numbers from 23 to 324 in a Column
"C".
The nType(i) Produces the Results Associated with the i Values in Column
"D".
Then at the Bottom the nTypeTotal Produces the Total Sum for the nType
Values.

nTypeTotal = ActiveCell.Row

For i = 23 To 324
ActiveCell.Offset(1, 0).Value = "Count for"
ActiveCell.Offset(1, 1).Value = i
ActiveCell.Offset(1, 2).Value = nType(i)
ActiveCell.Offset(1, 0).Select
Next i

Set rng = ActiveCell.Offset(1, 2)
rng.FormulaR1C1 = "=Sum(R" & nTypeTotal & "C:R[-1]C)"
rng.Formula = rng.Value

What I would like, is After the nTypeTotal ( Starting 2 Rows Down and
Continuing Down ), the Sum Total of the Following Please :-

If i >= 23 And If i <= 50 Then the nType Total Associated with i >= 23
And i <= 50.
If i >= 51 And If i <= 100 Then the nType Total Associated with i >= 51
And i <= 100.
If i >= 101 And If i <= 150 Then the nType Total Associated with i >=
101 And i <= 150.
If i >= 151 And If i <= 200 Then the nType Total Associated with i >=
151 And i <= 200.
If i >= 201 And If i <= 250 Then the nType Total Associated with i >=
201 And i <= 250.
If i >= 251 And If i <= 300 Then the nType Total Associated with i >=
251 And i <= 300.
If i >= 301 And If i <= 324 Then the nType Total Associated with i >=
301 And i <= 324.

Is this Possible Using Code like the nTypeTotal to Output the Values
Only, Instead of the Code Entering Formulas into the Worksheet Please.

Thanks in Advance.
All the Best.
Paul
 
Hi,

Has Anyone got Any Ideas for a Solution Please.
I Know I could Probably Setup 7 ( One for EACH Group ) Extra Loop
Counters to do this, But how would I get it to Total the nType for EACH
Group. This would Seem to be a Long Handed Way of doing this though.

Thanks in Advance.
All the Best.
Paul



Total a Variable Based on Another Variable Please
From: Paul Black
Date Posted: 3/29/2005 1:54:00 PM

Hi,

The Snippet of Code Below Works Great.
i is a Loop Counter that Produces the Numbers from 23 to 324 in a Column
C.
The nType(i) Produces the Results Associated with the i Values in Column
D.
Then at the Bottom the nTypeTotal Produces the Total Sum for the nType
Values.

nTypeTotal = ActiveCell.Row

For i = 23 To 324
ActiveCell.Offset(1, 0).Value = "Count for"
ActiveCell.Offset(1, 1).Value = i
ActiveCell.Offset(1, 2).Value = nType(i)
ActiveCell.Offset(1, 0).Select
Next i

Set rng = ActiveCell.Offset(1, 2)
rng.FormulaR1C1 = "=Sum(R" & nTypeTotal & "C:R[-1]C)"
rng.Formula = rng.Value

What I would like, is After the nTypeTotal ( Starting 2 Rows Down and
Continuing Down ), the Sum Total of the Following Please :-

If i >= 23 And If i <= 50 Then the nType Total Associated with i >= 23
And i <= 50.
If i >= 51 And If i <= 100 Then the nType Total Associated with i >= 51
And i <= 100.
If i >= 101 And If i <= 150 Then the nType Total Associated with i >=
101 And i <= 150.
If i >= 151 And If i <= 200 Then the nType Total Associated with i >=
151 And i <= 200.
If i >= 201 And If i <= 250 Then the nType Total Associated with i >=
201 And i <= 250.
If i >= 251 And If i <= 300 Then the nType Total Associated with i >=
251 And i <= 300.
If i >= 301 And If i <= 324 Then the nType Total Associated with i >=
301 And i <= 324.

Is this Possible Using Code like the nTypeTotal to Output the Values
Only, Instead of the Code Entering Formulas into the Worksheet Please.

Thanks in Advance.
All the Best.
Paul
 
Hi,

Would Setting 7 Case Statements in a Private Function be the Way to go
for a Solution to this Please.

Thanks in Advance.
All the Best.
Paul



Total a Variable Based on Another Variable Please
From: Paul Black
Date Posted: 3/29/2005 1:54:00 PM

Hi,

The Snippet of Code Below Works Great.
i is a Loop Counter that Produces the Numbers from 23 to 324 in a Column
C.
The nType(i) Produces the Results Associated with the i Values in Column
D.
Then at the Bottom the nTypeTotal Produces the Total Sum for the nType
Values.

nTypeTotal = ActiveCell.Row

For i = 23 To 324
ActiveCell.Offset(1, 0).Value = "Count for"
ActiveCell.Offset(1, 1).Value = i
ActiveCell.Offset(1, 2).Value = nType(i)
ActiveCell.Offset(1, 0).Select
Next i

Set rng = ActiveCell.Offset(1, 2)
rng.FormulaR1C1 = "=Sum(R" & nTypeTotal & "C:R[-1]C)"
rng.Formula = rng.Value

What I would like, is After the nTypeTotal ( Starting 2 Rows Down and
Continuing Down ), the Sum Total of the Following Please :-

If i >= 23 And If i <= 50 Then the nType Total Associated with i >= 23
And i <= 50.
If i >= 51 And If i <= 100 Then the nType Total Associated with i >= 51
And i <= 100.
If i >= 101 And If i <= 150 Then the nType Total Associated with i >=
101 And i <= 150.
If i >= 151 And If i <= 200 Then the nType Total Associated with i >=
151 And i <= 200.
If i >= 201 And If i <= 250 Then the nType Total Associated with i >=
201 And i <= 250.
If i >= 251 And If i <= 300 Then the nType Total Associated with i >=
251 And i <= 300.
If i >= 301 And If i <= 324 Then the nType Total Associated with i >=
301 And i <= 324.

Is this Possible Using Code like the nTypeTotal to Output the Values
Only, Instead of the Code Entering Formulas into the Worksheet Please.

Thanks in Advance.
All the Best.
Paul
 
Hi Paul,

I'm afraid I couldn't follow most of what you're doing except you say you
want a value in cells, not a formula. Try changing:

rng.Formula = rng.Value
to
rng.Value= rng.Value

Regards,
Peter T
 
Back
Top