| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a variable for MOD Statement
I make a report that has a varying number of rows and I use the variable
"MaxNumRows". The totals line for the report = MaxNumRows +3. Works great so far! I am using the cod =SUM(IF(MOD(ROW(R3C12:R(MaxNumRows)C12)-ROW(R3C12)+1,3)=0,R3C12:R(MaxNumRows)C12,0)) Doesn't work! I use MaxNumRows in other formulas; but it isn't working in THIS one. How can I make this formula become ...ROW(R3C12:R20C12) if the MaxNumRows = 20?? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
using a variable for MOD Statement
Are you trying to perform this calculation in VBA, or are you trying to
assign this formula to a cell? If you're trying to assign this formula to a cell, you probably want to do something like: Range("A1").FormulaR1C1 = "=SUM(IF(MOD(ROW(R3C12:R" & MaxNumRows & "C12)-ROW(R3C12)+1,3)=0,R3C12:R" & MaxNumRows & "C12,0))" Scott bethg7 wrote: I make a report that has a varying number of rows and I use the variable "MaxNumRows". The totals line for the report = MaxNumRows +3. Works great so far! I am using the code =SUM(IF(MOD(ROW(R3C12:R(MaxNumRows)C12)-ROW(R3C12)+1,3)=0,R3C12:R(MaxNumRows)C12,0)) Doesn't work! I use MaxNumRows in other formulas; but it isn't working in THIS one. How can I make this formula become ...ROW(R3C12:R20C12) if the MaxNumRows = 20?? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
using a variable for MOD Statement
YES!!! Thank you so much! You just saved me HOURS of work! BTW, it worked
like a charm! Thanks again. "Scott" wrote: Are you trying to perform this calculation in VBA, or are you trying to assign this formula to a cell? If you're trying to assign this formula to a cell, you probably want to do something like: Range("A1").FormulaR1C1 = "=SUM(IF(MOD(ROW(R3C12:R" & MaxNumRows & "C12)-ROW(R3C12)+1,3)=0,R3C12:R" & MaxNumRows & "C12,0))" Scott bethg7 wrote: I make a report that has a varying number of rows and I use the variable "MaxNumRows". The totals line for the report = MaxNumRows +3. Works great so far! I am using the code =SUM(IF(MOD(ROW(R3C12:R(MaxNumRows)C12)-ROW(R3C12)+1,3)=0,R3C12:R(MaxNumRows)C12,0)) Doesn't work! I use MaxNumRows in other formulas; but it isn't working in THIS one. How can I make this formula become ...ROW(R3C12:R20C12) if the MaxNumRows = 20?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Variable statement | Excel Discussion (Misc queries) | |||
| Variable in if statement | Excel Worksheet Functions | |||
| IF Statement Variable | Excel Discussion (Misc queries) | |||
| if than statement has too many variable | New Users to Excel | |||
| Macro Creating Variable and using variable in a SQL statement | Excel Programming | |||
Linear Mode
