Using code to insert different formulas
an alternative to the other answers would be to wrap the code into a couple
of UDFs
that way, changing the condition wouldn't change the formula, but would
generate the correct result
Function UDF_B(condition as reange)
dim rw as long
rw = condition.row
select case condition.Value
case 1 : UDF_B = cells(rw,"D") * cells(rw,"E")
case 2 : and so on for all conditions for column B formula
....
Case Else
End Select
End Function
your second function would be similar, but for column C formulae
Function UDF_C(condition as reange)
dim rw as long
rw = condition.row
select case condition.Value
case 1 : UDF_B = cells(rw,"F") * cells(rw,"G") / cells(rw,"H")
case 2 : and so on for all conditions for column B formula
....
Case Else
End Select
End Function
"Roger on Excel" wrote in message
...
[Excel 2003]
I have sixteen different conditions which can exist. These relate to
whether certain aspects are TRUE or FALSE. When taken together I have 16
unique "cases".
These appear in cells A1:A60 and are labeled Condition1, 2, 3 etc...
Condition16
Depending on the condition in each cell in column A, I need certain
formulas
to operate in adjacent cells in colomn B and C
For example for Condition1 in A1, I need the formula in B1 to be "=D1*E1"
and the formula in C1 to be "F1*G1/H1"
Each formula referes to cells in the same row, so perhaps some loop could
be
used to read down Column A1:A60 and populate the adjacent cells in Columns
B1:B60 and C1:C60 with the appropriate formula, depending on the Condition
or
"case"
I know from experience that one can enter formulas into specific cells
using
the following type of code
Range("B1").Select
ActiveCell.Formula = "=D1*E1"
So presumably one can create code to check Column A "Conditions" and
insert
the appropriate formulas?
Can anyone help?
Thankyou,
Roger
|