I have created a macro to sum a range of cells based on a condition in
a particular cell which works fine.
The overall procedure is quite long and the condition is used many
times to carry out different actions.
I would like to be able to change the condition in the formula using a
variable or constant rather than having to edit each instance of the
condition. The the condition, in this case 20, is the number of
characters in the first cell in a row. But depending on which data
the procedure is being applied to it could be 4, 8, 12, etc. I would
like to do something such as declare a constant "Const Level01 As
Integer = 20" and then replace 20 in the formula with the value of
the constant or perhaps a variable. It doesn't change value in the
running of the procedure so I thought a constant would best. It might
be something that I would want to derive the value from using an input
box with changeable default values at some stage, but one step at a
time.


Case Is = "Total"
Cell.Offset(0, 11).FormulaArray = "=(SUM(IF(LEN
(R6C1:R5000C1)20,R6C11:R5000C11,0)))"
With Cell.EntireRow
.RowHeight = 36

I've struggled to make it work and would be grateful for any help
given.
Ritchi

Hi Ritchi

Try this:

Static Level01 As Long
Level01 = 20
Case Is = "Total"
myformula = _
"=(SUM(IF(LEN(R6C1:R5000C1)" & Level01 & ",R6C11:R5000C11,0)))"
ActiveCell.Offset(0, 11).FormulaArray = myformula

Regards,
Per
"RITCHI" skrev i meddelelsen
...
I have created a macro to sum a range of cells based on a condition in
a particular cell which works fine.
The overall procedure is quite long and the condition is used many
times to carry out different actions.
I would like to be able to change the condition in the formula using a
variable or constant rather than having to edit each instance of the
condition. The the condition, in this case 20, is the number of
characters in the first cell in a row. But depending on which data
the procedure is being applied to it could be 4, 8, 12, etc. I would
like to do something such as declare a constant "Const Level01 As
Integer = 20" and then replace 20 in the formula with the value of
the constant or perhaps a variable. It doesn't change value in the
running of the procedure so I thought a constant would best. It might
be something that I would want to derive the value from using an input
box with changeable default values at some stage, but one step at a
time.


Case Is = "Total"
Cell.Offset(0, 11).FormulaArray = "=(SUM(IF(LEN
(R6C1:R5000C1)20,R6C11:R5000C11,0)))"
With Cell.EntireRow
.RowHeight = 36

I've struggled to make it work and would be grateful for any help
given.
Ritchi