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

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

Thank you very much

Sorry for not explaining the nature of my problem earlier it is an
engineering problem for designing the steel wind bracing members in
multistory building.

A E F G H I J k
Profile Force lb λmax Fc all Fc act Cond. Cond.
80x8 5.86 350.00 144.03 0.36 0.24 TRUE TRUE
90x9 12.00 350.00 128.21 0.46 0.39 TRUE TRUE
100x10 20.00 350.00 115.13 0.57 0.52 TRUE TRUE

1. The drop down list in cell A selects between 6 different types of steel
profiles based on table B in my first post.
2. I used the following formula in cell B, C, D
=VLOOKUP($A3,$A$9:$D$14,COLUMNS($B3:C3),0)
To link the proper profile to its Area, ix & P so when I select a profile
from the dropdown list its information is updated automatically.
3. Cell E is the force in the bracing member.
4. Cell F is the buckling length of the bracing member.
5. Cell G is a factor the Formula is =F3/C3
6. Cell H is for allowable sheer the formula is =7500/(G3*G3)
7. Cell H is the actual sheer the formula is =E3/(B3*2)
8. Cell J is a condition the formula is =IF(I3<=H3,TRUE,FALSE)
9. Cell K is a condition the formula is =IF(G3<=200,TRUE,FALSE)
10. Cell L is the number of stories in the building
11. I start in my table with a building of one story than two stories and so
on until I reach 20 stories by than my table contains 223 cells
12. What I am trying to do is to automate the process instead of going in
each (A) cell in the table and selecting manually the correct first profile
that gives TRUE answer to both conditions I run a macro or a formula instead.

Best Regards,

Sherif


"Joel" wrote:

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 A3
=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