I found some problems with my code. Keep I making it better. My code will
automatically fill in column N. You can't make it A3 because A3 keeps on
changing. Use the new code below.

My problems were not the problem you posted. The problem you posted is due
to the fact you don't have a validation list in either A3, AA3, or BA3 on
every sheet of your workbook. Either delete worksheets that don't have the
data, add the missing validation lists, or change the line below.

ValidationGroup = Array("", "A", "B")

The line above moves across three group sin the spreadsheet. MY code only
looks at cell A3 and columns J, columns K, and fills in column N. the
ValidationGroup simply put either a blank, an A, or a B in front of the
column letter. For example A3, AA3, BA3.

If you only have one or two groups then change the line to
ValidationGroup = Array("")
ValidationGroup = Array("", "A")




Sub sim_list()

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)

RowCount = 3
Do While .Range(Group & "J" & RowCount) < ""
Found = False

For Each cell In VRange
.Range(Group & "A3") = cell
If .Range(Group & "J" & RowCount) = True And _
.Range(Group & "K" & RowCount) = True Then

Found = True
Exit For
End If
Next cell
If Found = False Then
.Range("N" & RowCount) = "Error"
Else
.Range("N" & RowCount) = cell
End If

RowCount = RowCount + 1
Loop
Next Group
End With
Next sht
End Sub


"Sherif" wrote:

Thank you very much

I am working on my Master thesis myself it is a cost optimization using
neural networks I am waiting for my supervisor to select the software I am
going to use.

You are right in both comments it is a sequential series & I do need the
first TRUE answer for story 1, then the first TRUE answer for story 2, and so
on until story 20

I added the following columns to the table

L M N
Buildingsize FloorNumber Profile
1 1
2 1
2 2

In column N should I make the formula N3=A3

When I run the code debug highlights the following lines

ValidationRange = _
.Range(Group & "A3").Validation.Formula1

Run time error 1004:
Application defined or object defined error

Best Regards,

Sherif

"Joel" wrote:

I went back and looked at my latest code and found that I should of switched
the order of some of the loops. I think this code is better than my last
posting

Sub sim_list()

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)

RowCount = 3
For Buildingsize = 1 To 20
For FloorNumber = 1 To Buildingsize
.Range("M" & RowCount) = FloorNumber
Found = False

For Each cell In VRange
.Range(Group & "A3") = cell
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
Loop
If Found = False Then
.Range("N" & RowCount) = "Error"
Else
.Range("N" & RowCount) = cell
End If

RowCount = RowCount + 1
Next cell
Next FloorNumber
Next Buildingsize
Next Group
End With
Next sht
End Sub

"Joel" wrote:

I'm an engineer too. BSEE, and MSCS, and two graduate certificates (EE and
Sytem eng). Have enough course credits for a Phd.

Looks like you have a sequental series 1, 2, 3, 4, ...., 19, and 20. The
total is 221 but you are starting in Row 3 your range goes to row 223.

I think youy need the first TRUE answer for story 1, then the first TRUE
answer for story 2, and so on until story 20.

I would make column M the story number and and column N the type of steel
required taken from A3.


Sub sim_list()

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
For Buildingsize = 1 To 20
For FloorNumber = 1 To Buildingsize
.Range("M" & RowCount) = FloorNumber
Found = False
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
Loop
If Found = False Then
.Range("N" & RowCount) = "Error"
Else
.Range("N" & RowCount) = cell
End If

RowCount = RowCount + 1
Next FloorNumber
Next Buildingsize
Next cell
Next Group
End With
Next sht
End Sub


"Sherif" wrote:

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.