Simulation
I added an increment of the rows, but really don't understand what you are
trying to do. I'm confused wjy the validation list is even needed for the
simulation.
A true simulation would have only one row and use A3 to make the
calucaltion. For example.
The area would be based on the item selected in cell A
=VALUE(LEFT(TRIM(MID(A$3,2,LEN(A$3))),FIND("x",TRI M(MID(A$3,2,LEN(A$3))))-1))*VALUE(TRIM(MID(A$3,FIND("x",A$3)+1,LEN(A$3))))/52.16
This complicated fromula extracts the two numbers in the string 80x8 and
multiplies the two numbers together (640) and then divides by 52.16 to get
your area number. the simulation would keep on changing the selected number
in the Pull-down list until True was found in both column J and K.
You don't seem to need a simulation, but just to go down the rows until
column J and K are both true.
Sub sim_list()
Found = False
For Each sht In ThisWorkbook.Sheets
With sht
ValidationGroup = Array("", "A", "B")
For Each Group In ValidationGroup
ValidationRange = _
.Range(Group & "A3").Validation.Formula1
'remove equal sign
ValidationRange = Mid(ValidationRange, 2)
Set VRange = .Range(ValidationRange)
For Each cell In VRange
.Range(Group & "A3") = cell
RowCount = 3
Do While .Range(Group & "J" & RowCount) < ""
If .Range(Group & "J" & RowCount) = True And _
.Range(Group & "K" & RowCount) = True Then
Found = True
Exit Do
End If
RowCount = RowCount + 1
Loop
If Found = True Then Exit For
Next cell
If Found = True Then Exit For
Next Group
End With
If Found = True Then Exit For
Next sht
End Sub
"Sherif" wrote:
Thank you very much could you please help me with the following:
A J K
Profile Area ix P Cond. Cond.
3 L 80x8 12.27 2.43 9.63 TRUE TRUE
4 L 90x9 15.52 2.73 12.18 TRUE TRUE
223 L120X11 25.37 3.66 19.92 TRUE TRUE
1. When I run the simulation only the first row containing A3,J3 & K3 is
effected the following rows A4 up to A223 remains static.
2. As you have pointed out the simulation needs to be run for each row as
each simulation is separate.
3. I tried removing the (If Found = True Then Exit For) however it didnt
work.
Best Regards,
Sherif
"Joel" wrote:
I don't know if each simulation is seperate or combined. You may need to
remove one or two of the following statement
If Found = True Then Exit For
Sub sim_list()
Found = False
For Each sht In ThisWorkbook.Sheets
With sht
ValidationGroup = Array("", "A", "B")
For Each Group In ValidationGroup
ValidationRange = _
.Range(Group & "A3").Validation.Formula1
'remove equal sign
ValidationRange = Mid(ValidationRange, 2)
Set VRange = .Range(ValidationRange)
For Each cell In VRange
.Range(Group & "A3") = cell
If .Range(Group & "J3") = True And _
.Range(Group & "K3") = True Then
Found = True
Exit For
End If
Next cell
If Found = True Then Exit For
Next Group
End With
If Found = True Then Exit For
Next sht
End Sub
"Sherif" wrote:
Thank you very much could you please help me with the following:
1. The cells containing the dropdown list will be A3 to A223 and the cells
containing the TRUE argument corresponding to that are J3 to J223 & K3 to
K223.
2. This same thing will be repeated for cells AA3 to AA223, AJ3 to AJ223 &
AK3 to AK223.
3. And repeated again for cells BA3 to BA223, BJ3 to BJ223 & BK3 to BK223.
4. My workbook contains 8 worksheets is there is a way to apply the code to
all the worksheets.
Best Regards,
Sherif
"Joel" wrote:
I wasn't sure if J3 & K3 were fixed or need to be change to AJ3, AAJ3, AAJ3
so I did both cases
Sub testA()
Set ValidationGroup = Range("A3", "AA3", "AAA3")
For Each GroupCell In ValidationGroup
ValidationRange = GroupCell.Validation.Formula1
'remove equal sign
ValidationRange = Mid(ValidationRange, 2)
Set VRange = Range(ValidationRange)
For Each cell In VRange
GroupCell = cell
If Range("J3") = True And _
Range("K3") = True Then
Exit For
End If
Next cell
Next GroupCell
End Sub
Sub testB()
Set ValidationGroup = Range("", "A", "AA")
For Each Group In ValidationGroup
ValidationRange = Range(Group & "A3").Validation.Formula1
'remove equal sign
ValidationRange = Mid(ValidationRange, 2)
Set VRange = Range(ValidationRange)
For Each cell In VRange
Range(Group & "A3") = cell
If Range(Group & "J3") = True And _
Range(Group & "K3") = True Then
Exit For
End If
Next cell
Next Group
End Sub
"Sherif" wrote:
Thank you very much the code worked perfectly
I would like to run this code 3 times for cell A3, AA3 and AAA3 in the same
worksheet could you please explain how to do it.
Best Regards
Sherif
"Joel" wrote:
The only way I found to do the simulation is to actually put the data into
A3. the code below gets the drop down list data and puts it into A3
Sub test()
Set a = Range("A3")
ValidationRange = Range("A3").Validation.Formula1
'remove equal sign
ValidationRange = Mid(ValidationRange, 2)
Set VRange = Range(ValidationRange)
For Each cell In VRange
Range("A3") = cell
If Range("J3") = True And _
Range("K3") = True Then
Exit For
End If
Next cell
End Sub
"Sherif" wrote:
Hi ,
I am trying to run a simulation the data that I have are in the following
tables.
A3 (A) J3 k3
Profile Area ix P Cond. Cond.
L 80x8 12.27 2.43 2.43 TRUE TRUE
(B)
Profile Area ix p
L 70x7 9.4 2.12 7.38
L 80x8 12.27 2.43 9.63
L 90x9 15.52 2.73 12.18
L 100x10 19.15 3.04 15.04
L 110X10 21.15 3.36 16.61
L120X11 25.37 3.66 19.92
1. In table (A) cell (A3) I have a dropdown list that contains 6 choices
from (L70*7 to L120*11)
2. Depending on the choice I select from the dropdown list 2 logical
arguments are effected Cell (J3) (=IF(I3<=H3,TRUE,FALSE)) and Cell (K3)
(=IF(G3<=200,TRUE,FALSE)).
3. In this case the choice of (L80*8) from the dropdown list gives the
correct answer as both arguments in cell (J3 & K3) return with the answer
(TRUE) which what I want.
4. Is there a way where I can run the 6 choices in the dropdown list like I
would in a simulation?
5. Where the simulation starts with the choice (L70*7) and ends with
(L120*11).
6. When Both Cells (J3 & K3) gives the answer (TRUE) than the simulation ends.
7. In the example I have given in (3) the simulation will end at (L80*8).
The first choice the gives (TRUE) answer.
8. If (False) answer is found in the 6 choices than the simulation ends with
L120*11.
Best Regards,
Sherif
|