Simulation

[Sherif]

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

 

[Joel]

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]

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]

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]

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]

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]

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 didn’t
work.

Best Regards,
Sherif

 

[Joel]

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",TRIM(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]

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]

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

 

[Joel]

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

 

[Sherif]

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]

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]

Thank you very much

I changed the line as you recommended to ValidationGroup = Array("")
I will now work with one group only.
I Also Left column N empty.

The code now runs with no error report however I am having this problem:

The first row A3 the correct profile is listed correctly in N3 which is L80*8
The second row A4 the wrong profile is listed in N4 Error instead of L90*9
The Fifth row A5 the wrong profile is listed in N5 Error instead of L100*10
And so on for the following rows.

A E J K N
Profile Force Cond. Cond. Profile
L120X11 5.86 TRUE TRUE L 80x8
L 70x7 12.00 FALSE TRUE Error
L 70x7 20.00 FALSE TRUE Error

Best Regards,

Sherif

 

[SteveM]

Thank you very much

I changed the line as you recommended to ValidationGroup = Array("")
I will now work with one group only.
I Also Left column N empty.

The code now runs with no error report however I am having this problem:

The first row A3 the correct profile is listed correctly in N3 which is L80*8
The second row A4 the wrong profile is listed in N4 Error instead of L90*9
The Fifth row A5 the wrong profile is listed in N5 Error instead of L100*10
And so on for the following rows.

A E J K N
Profile Force Cond. Cond. Profile
L120X11 5.86 TRUE TRUE L 80x8
L 70x7 12.00 FALSE TRUE Error
L 70x7 20.00 FALSE TRUE Error

Best Regards,

Sherif















...

read more »

Pardon me for being pragmatic but why the heck are you coding your own
simulation when there are plenty of sim packages you can buy off the
shelf. Some good Excel add-ins BTW. Now I don't mean you as a
student buy personally, but doesn't your department or computer lab
have sim software available for you. Not only do they make it easier
to build and manage the sim, but output management is superior to
something you code from scratch. And you can hook up an optimizer to
a sim if you want through iterative VB function calls.. Moreover, if
you are doing cost optimization, there almost always correlated
variables. And building in a correlation strategy on your own can be
a mess. But without one your results could be pretty skewed.

Just my humble opinion. But I've been around the re-inventing the
wheel block and it can be a long trek.

Good Luck,

SteveM

 

[Joel]

I don't think there is any thing wrong with the code. Check row 4 manually
by going through all the pulldown options in cell A3 and see if any of the
types of steel produces a True condition in both cells J4 and K4.

 

[Sherif]

Thank you very much

I checked manually row 4 L90*9 produces TRUE condition in both J4 & K4
And in row 5 L100*10 produces TRUE condition in both J5 & K5

Best Regards,

Sherif

 

[Joel]

I checked the code below and it works fine. I commendted out pat of the
array function to eliminate the other problem we had. The only reason I see
the code not working is if ture is a string instead of the logical true.
Make sure there aren't any spaces or quotes in the formulas for True and
False.

Otherwise, you will need to debug the code. You can step through the code
in VBA by pressing F8 repetively. You can also flip back and forth between
the worksheet and the VBA code and watch the code as A3 gets changed. Right
now I'm confused.

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