Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code to insert different formulas
[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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code to insert different formulas
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 -- Jacob "Roger on Excel" wrote: [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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code to insert different formulas
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 Roger on Excel wrote: [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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert formulas | Excel Programming | |||
Code to Insert rows and copy formulas | Excel Programming | |||
Macro to insert copy and insert formulas only to next blank row | Excel Programming | |||
formulas after insert row | Excel Programming | |||
code to insert add'l code ??? | Excel Programming |