Using code to insert different formulas

[Roger on Excel]

[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

 

[Jacob Skaria]

Use Select case Statement as below.. Checkout the below link for help
http://www.vbtutor.net/lesson8.html..

The below will check the value of colA if text "A" exits assign cell B and C
with formula

Sub Macro()
Dim cell As Range
For Each cell In Range("A1:A16")
Select Case cell.Value
Case "A"
cell.Offset(, 1).FormulaR1C1 = "=RC[2]*RC[3]"
cell.Offset(, 2).FormulaR1C1 = "=RC[3]*RC[4]/RC[5]"
Case "B"
'formula to be altered to suit
'cell.Offset(, 1).FormulaR1C1 = "=RC[2]*RC[3]"
'cell.Offset(, 2).FormulaR1C1 = "=RC[3]*RC[4]/RC[5]"
Case "C"
'formula to be altered to suit
'cell.Offset(, 1).FormulaR1C1 = "=RC[2]*RC[3]"
'cell.Offset(, 2).FormulaR1C1 = "=RC[3]*RC[4]/RC[5]"
End Select
Next
End Sub

 

[keiji kounoike]

This one is almost same code as Jacob's, just make a helper function to
ease to put your formula. Function Myformula takes two arguments, one is
range where conditions reside and the other is a formula's string. In a
formula's string, "@" stand for number of the row. I assume condition in
column A is like "Condition1", "Condition2", "Condition3" and the like.

Sub Mytest()
Dim rng As Range
For Each rng In Range("A1:A5") '<<==Change to your Range
Select Case LCase(rng)
Case "condition1"
rng.Cells(1, 2) = Myformula(rng, "=D@*E@")
rng.Cells(1, 3) = Myformula(rng, "=F@*G@/H@")
Case "condition2"
rng.Cells(1, 2) = Myformula(rng, "=(D@+E@)*H@")
rng.Cells(1, 3) = Myformula(rng, "=ROUND(F@*G@,2)")
Case "condition3"
rng.Cells(1, 2) = Myformula(rng, "=D@-E@")
rng.Cells(1, 3) = Myformula(rng, "=SIN(F@*G@)")
'add Case below

End Select
Next

End Sub

Function Myformula(rng As Range, Formulastr As String) As String
Myformula = Replace(strf, "@", rng.Row)
End Function

Keiji

 

[Patrick Molloy]

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