Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-22-2017, 01:18 PM
Kopko Kopko is offline Using an undefined number of conditions to give a final formula Windows 10 Using an undefined number of conditions to give a final formula Office 2013
Novice
Using an undefined number of conditions to give a final formula
 
Join Date: May 2017
Posts: 12
Kopko is on a distinguished road
Default 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
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

Last edited by Kopko; 08-22-2017 at 01:27 PM. Reason: result desired
Reply With Quote
Reply

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
Using an undefined number of conditions to give a final formula 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft