Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
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 | |||
Total a Variable | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
variable counter/total | Excel Programming |