Total a Variable Based on Another Variable Please
 

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Total a Variable Based on Another Variable Please
 

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




*** Sent via Developersdex http://www.developersdex.com ***

Total a Variable Based on Another Variable Please
 

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


*** Sent via Developersdex http://www.developersdex.com ***

Total a Variable Based on Another Variable Please
 

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

"Paul Black" wrote in message
...
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


*** Sent via Developersdex http://www.developersdex.com ***