#1
|
|||
|
|||
Using an undefined number of conditions to give a final formula
I have two conditions that follows a certain pattern like this: - ABC is a three letters string defined by the user - #,@,$,*,% are all different numbers also defined by the user (can be 3 or 4 digits) Condition 1: ('ABC_###' = "on" and 'ABC_@@@@' = "on") Condition 2: ('ABC_$$$' = "off" and ('ABC_****' = "on" or 'ABC_%%%' = "on")) And these conditions can repeat with different numbers depending on the situation. The user will choose which and how many conditions there are, and after that the code would give a final formula grouping all the conditions. Above there is an example with 4 conditions: Final Formula: "If (Condition 1 or Condition 2 or Condition 1 or Condition 1) then 0 else 1" What I did so far: I create multiple inputbox to the user type the letters and the keys that he will use. After that, I create two routines for each condition. My code is working if the user type only two conditions (just one condition 1 and one condition 2) My problem: When I go to give the final formula, I don't know how to include all the conditions in the formula. My code is working just for two conditions like above. I thought in making an array to hold the conditions but still having problems. I'm not sure how to do that because the user can type 2 conditions or maybe 10. Depends on the situation. How could I give the final formula correctly? Reinforcing that my formula will be exactly like the example "Final Formula", the only thing that changes is the amount of conditions (and the type). Anyone could help me please? Code:
Sub teste() Dim c1, c2, formula As String Dim lastrow As Long START: cond = InputBox("Press 1 for Condition 1. Press 2 for Condition 2. Press 3 to Finish") If cond = 1 Then GoTo COND1 ElseIf cond = 2 Then GoTo COND2 ElseIf cond = 3 Then GoTo FINAL End If COND1: abc = InputBox("type mnemonic") x1 = InputBox("type number of first key") x2 = InputBox("type number of second key") c1 = Chr(40) + Chr(39) + abc + "_" + x1 + Chr(39) + " = " + Chr(34) + "on" + Chr(34) + " and " + Chr(39) + abc + "_" + x2 + Chr(39) + " = " + Chr(34) + "on" + Chr(34) + Chr(41) GoTo START COND2: abc = InputBox("type mnemonic") x1 = InputBox("type number of first key") x2 = InputBox("type number of second key") x3 = InputBox("type number of third key") c2 = Chr(40) + Chr(39) + abc + "_" + x1 + Chr(39) + " = " + Chr(34) + "off" + Chr(34) + " and " + Chr(40) + Chr(39) + abc + "_" + x2 + Chr(39) + " = " + Chr(34) + "on" + Chr(34) + " or " + Chr(39) + abc + "_" + x2 + Chr(39) + " = " + Chr(34) + "on" + Chr(34) + Chr(41) + Chr(41) GoTo START FINAL: formula = "If " + c1 + " or " + c2 + " then 0 else 1" lastrow = Cells(Rows.Count, 1).End(xlUp).Row Cells(lastrow, 1) = formula Exit Sub End Sub Quote:
Last edited by Kopko; 08-22-2017 at 01:27 PM. Reason: result desired |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula Assistance for restricting the number of for analysis | gspikesr | Excel | 3 | 05-02-2016 11:28 PM |
Excel Formula for Multiple Conditions & Results | nowco | Excel | 4 | 03-16-2016 11:24 AM |
Formula to add number of pallets shipped | piglovindillard | Excel | 2 | 08-13-2012 06:40 AM |
6 conditions formula | paconovellino | Excel | 2 | 03-05-2012 06:45 AM |
change "final showing markup" default to "final" | Wireframe | Word | 0 | 06-25-2010 11:35 AM |