Gayathri Venkatesh
New Member
- Joined
- Jan 18, 2006
- Messages
- 25
Hi,
I am using follwing formula in macro
ActiveCell.FormulaR1C1 = "=SUMPRODUCT((INSTALLrange>0)*((BILLONLYrange)>0)*((AUrange)>0))"
Installrange,billonlyrange & aurange are defined names used for storing range
Value of Aurange changes depending on country.
i would like to use looping for change in aurange value as the formula is used more than once with variation for a given country. i cannot assign value to aurange dynamically as values for all countries will change with the change in value of aurange. i also tried using a variable say varrange and assign defined names (say aurange, nzrange) i.e. value within value for e.g
ActiveCell.FormulaR1C1 = "=SUMPRODUCT((INSTALLrange>0)*((BILLONLYrange)>0)*((varrange)>0))"
and assign
varrange = aurange for AU
varrange = nzrange for NZ
but it does not work.
Any easier way of dealing with it.
Thanks
I am using follwing formula in macro
ActiveCell.FormulaR1C1 = "=SUMPRODUCT((INSTALLrange>0)*((BILLONLYrange)>0)*((AUrange)>0))"
Installrange,billonlyrange & aurange are defined names used for storing range
Value of Aurange changes depending on country.
i would like to use looping for change in aurange value as the formula is used more than once with variation for a given country. i cannot assign value to aurange dynamically as values for all countries will change with the change in value of aurange. i also tried using a variable say varrange and assign defined names (say aurange, nzrange) i.e. value within value for e.g
ActiveCell.FormulaR1C1 = "=SUMPRODUCT((INSTALLrange>0)*((BILLONLYrange)>0)*((varrange)>0))"
and assign
varrange = aurange for AU
varrange = nzrange for NZ
but it does not work.
Any easier way of dealing with it.
Thanks