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
The result desired would be something like this:
Quote:
|
If ('BBB_123' = "on" and 'BBB_124' = "on") or ('BBB_233' = "off" and ('BBB_234' = "on" or 'BBB_234' = "on")) then 0 else 1
|
But this is just working if the user type just 1 or two conditions