If statements with dynamic variables?

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
I need to write an if statement based on some conditions which MUST be true and then other conditions of which any "4" (the value 4 needs to be an input by the user) can be true.

However, I need the user to be able to determine which conditions are "MUST" conditions. I will have the user indicate whether a condition is a "MUST" have with a list box which will then put a "1" in a cell against that condition indicating it is a "MUST" condition. There are 8 conditions of which any can be "MUST" and the rest are other.

Example:

Sub condition_analysis()Dim data_set As Variant
data_set = Range("A4:AN7500")
Dim i As Long
For i = 1 To UBound (data_set, 1)
factor_1 = data_set(i, 10)
factor_2 = data_set(i, 9)
factor_3 = data_set(i, 8)
If factor_1 > 0 Then
factor_1 = 1
End If
If factor_2 > 0 Then
factor_2 = 1
End If
If factor_3 > 0 Then
factor_3 = 1
End If
If factor_1 = 1 And (factor_2 + factor_3 + factor_4) > 1 Then 'Here is my problem - in this example factor_1 is a "MUST" condition while the others are not. But I need which conditions are MUST conditions (and their associated positioning in the code) to be determined by the user (cell values based on list box); how do I do this?
condition_satisfied = True
End If 'I then go on to do calculations on other data based on whether the condition_satisfied is True or not
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

cfadr

New Member
Joined
Oct 17, 2019
Messages
28
No - I have two different/separate issues I am trying to figure out - 1) how to dynamically determine which conditions are MUST conditions etc. per the above and 2) how to have a dynamic "look back period" for a condition which is the question I asked under a different post (I thought better to separate them given they are different problems). Thanks
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
Ok, the lack of code tags makes your code messy and hard to read, at a quick glance they appeared to have very similar principles.

I'm not following exactly what you need from your example, see if this method helps
Code:
Dim must_check As Boolean
Dim optional As Long
Dim satisfied As Boolean
If factor_1_must = True Then ' check if factor is a must
    If factor_1 = 0 Then must_check = False ' if factor is a must check that a value has been entered
Else
    If factor_1 <> 0 Then optional = optional + 1 ' if factor is not a must then add 1 to optional if factor has a value
End If

If must_check = True And optional > 0 Then satisfied = True
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,416
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top