hellllllp. (- multiple posts b/c of errors posting possibly)

[Derrick]

so here's the situation:
1. i have to check deflection of a horizontal beam as a result of glass
weight on it.
if deflection is less than the acceptable limit, then everything is peachy.
if not, then i have problems.
- one option, and the most popular over here, is to reinforce it by
inserting a steel member (tube or bar or channel etc).
the problem with doing this is that the deflection caused by the steel's
weight is often larger than the strength it can provide with its 'Iy'
property. (the higher the Iy, the smaller the amount of deflection.)
so. now i have a table of steel, and its properties. - Ix, Iy, weight, etc.
on another sheet.
2. if there is a problem with the deflection, i i would like to do an
automatic check through the list of steel to see which members have high
enough Iy value to counter: the original deflection + the deflection caused
by the steel's weight.

so. can I:
1. use a loop in a macro to go through my list of steel
2. (using a formula already set up) return a 'yes' or 'no' depending on
whether or not the above criteria are met.
3. click on a 'recommended steel type' cell block, and have a list of steel
members which work drop down (from the returned 'yes' or 'no' loop)
ie: A B
1 # description
2 101 2"x4" bar
return: 101 - " 2"x4" bar " in another cell
4. have the selected steel member display its information on the sheet so i
can see its properties and then calculate results which solve the deflection.

i know. it's alot to ask. any help would be spectacularly appreciated.
maybe a cake will be baked for you. Who knows...

 

[AB]

It surely can be done.
What's the exact structure of your 'Steel' table/data?
What data exactly would be takend from your input table/data and passed over
to your 'Steel' data for checking?

 

[Derrick]

hey AB. Now that the weekend is over,

Steel table/data:
a b c d
item name description Iy
1... bar 2x3 4.500
100 channel c3x4.1 1.70
101 channel c3x5.4 2.1
etc.

calc sheet:
a b c d
e ...
1 Max deflection min Iy item
item
2 allowable actual required number
description
3 0.5 .62 2.1 (dropdown here)
(when checked,
(001 - Bar 3X2
return info here)
..101 - Channel C3x5.4)
(channel- C3X5.4)

note that the cell blocks aren't the exact cell blocks, but only used for
reference.
also, i have 3 criteria i have to meet. Max deflection, Stress 1, Stress 2.
if any of these are over, i ahve to check for a suitable steel type. so...
max() something.
if there is no steel available.. can there be a "not possible" option show up?

 

[AB]

so, i've got 4 columns in Steel sheet:

Item
Name
Description
Iy

The Calc sheet structure got somewhat grabbed in your post. Could you
clarify what columns do you have in ther? I must be missing some
fields, am i?:
Max Deflection allowable
min Iy
item number
item description
dropdown

Also - how many entries you'd expect to have on the Calc Sheet? Always
just one and the dropdown would be 'effective' for that one or you'd
keep appending new entries all the time to the list?

 

[Derrick]

ya, sorry i noticed the columns later.
4 columns in Steel sheet ( actually there are more, but transferring of data
can be duplicated - (1)Number, (2)Name, (3)Description, (4+) Iy, Ix, Sy,
Sx,etc. - so i just put Iy, under the assumption that getting info will be
easy - using Lookup(), once the item number is selected.)
8 relevant columns in the Calc sheet
- max allowable/actual deflection (2)
- max allowable/actual stress1 (2)
- max allowable/actual stress2 (2)
- these three are similar - in that if one goes over, i need to get
steel to fix the problem, but the steel should be large enough to solve all
three problems - or using a max() function i think.
- dropdown list (where item number and deflection are displayed in dropdown
list, but only the item number is displayed when one is checked. if not
possible, then everything can be shown (description and number)) (1)
- and a desription column, where if above is possible, the descripion will
be displayed in a seperate column. (1)

as for the number of entries in the dropdown list, i would like to have all
possible steel selections shown. - that way we can select one depending on
what we need for the job

does this help?

 

[AB]

yeap, it does help.
My bad - my question regarding the count of entries wasn't clear
enough - what tried to find out is:
How many rows you'd anticipate in the Calc sheet?
Meaning - would you populate the 8 columns in row 2 and then select
the dropdonw (still in row 2) depending on what the dropdown let's you
select.
Then, when you get your next task/steel (i don't know how to call it -
that's whe you have your next need to calculate/select the appropriate
bars), would you enter the data in the same 8 columns in row 3, select
the dropdown.
Then in row 4..
etc.
OR
you'd always utilize just row number 2 in there - i.e., whenever you
need to get the appropariate available bar types you'd just overwrite
the data in the row 2?
The latter would be simplier and maybe even i could figure out how to
achieve that just with formulas (no macros/vba).

Is my question any clearer now?

 

[Derrick]

ahh ok. i understand.
I will use different rows.
ie - for 4 diff calculations, i will use 4 rows.
that way, when everything is done, i can tally up all the steel needed.
so: 'no' to overwriting
possibly, i'll be using up to 30 rows.. it will depend on the amound of
calcs needed.

so im afraid the simpler answer is not possible..

 

[AB]

Hey Derrick,

We're in different timezones, therefore the lag.
So, i've managed to come up with a formula (as opposed to vba/macro)
solution. It's not yet as sophisticated as you wanted but it actually
does work and could be a good basis to buld on for further
enhancements.
The solution uses a couple of Array formulas and named ranges (one of
which is a 'relative' named range (as opposed to 'absolute' named
range) - one needs to be cautios with those but they do work and i've
been using them successfully for a while).

It could be quite tricky for me to explain here the whole thing and
then for you to try to replicate that at yours (i mean it could take
up to an hour or more), so if i could just forward you the workbook,
it might save us both some time. As i said, the file contains only
formulas and no macro - so no threat to your system. Let me know if it
would work for you and the email address i should send the file to.
If sending the file doesn't work for you, post back and i will try to
explain how the file works in here.

 

[Derrick]

ya you might as well send it. the email address is (e-mail address removed)
i would prefer it if this email does not receive spam... that goes for
everyone reading this
thanks for your help!

 

[AB]

Sent.
Let me know how you're getting on.

ya you might as well send it. the email address is (e-mail address removed)
i would prefer it if this email does not receive spam... that goes for
everyone reading this
thanks for your help!








- Show quoted text -

 

[Derrick]

its not quite what im looking for.

i think i understand the premise, but i was hoping not to have another page
with intermediate calculations (as i'm trying to make this a calc package
workbook)

can you do it with code? i have a feeling that that might be a bit better..
ie:

if Deflection Actual >=Deflection Allowable, then
x=0
for(steel Item Column=1, steel item column=(last - like 250),x++)
x:=x+1
then some code where i run through whether or not adding the steel item
will solve my deflection problem

if deflection is solved, then
then some code which will add it to a dropdown list
end if
end loop
else if dropdown list is empty then
put 'no solution' in a dropdown list
end if

and set it up in the Worksheet code for each cell.
and then do multiple if statements for deflection stress combined... and
check to make sure stress is ok too.

does this make sense?

 

[jasontferrell]

Let me start with this...it's a complicated problem, but maybe this
will help. I'm going to assume that there is one sheet with the
members you want to test, with all their properties in columns, one
member per row. Then there is another sheet with the list of steel
supporting members that might fit the bill if the primary member's
deflection is too high. shtSteel is the list of supporting members
and shtTestMember is the list of primary members. My concept would be
to start on one row of the members you'd like to test and trigger the
routine below. This assumes you've created a userform with a single
combobox. If the deflection in your test member is too high, it would
test each available steel (my calculations are bogus, just for
example) to see if it would work. If it does, it adds it to the
combobox. At the end of the test for all the steels, the userform is
displayed. Then you could use some code in the userform to add the
selected steel to the row in the primary (test) member's sheet, saving
it there to show which one you chose. You could also set this routine
to run for all the rows in the primary member sheet, but this is just
a start.

Option Explicit
Public Sub PopulateAcceptList()
Dim shtSteel As Worksheet, shtTestMember As Worksheet
Dim lRow As Long
Dim lCurrent As Long
lCurrent = ActiveCell.Row
Set shtSteel = Sheets("Steel List")
Set shtTestMember = Sheets("Members")
'clear out old combobox items
UserForm1.ComboBox1.Clear
For lRow = 1 To shtSteel.UsedRange.Rows.Count
'calculations here to compare some calculation of the steel to
'what would be required for the member
If shtSteel.Cells(lRow, 3) > shtTestMember.Cells(lCurrent, 4)
Then
'this steel works
UserForm1.ComboBox1.AddItem shtSteel.Cells(lRow, 1).Value
End If
Next lRow
UserForm1.Show
End Sub

 

[Derrick]

Thanks Jason. this is kinda what i was hoping for.

however, im not quite sure whats going on in the code.
My sheet names are Steel and Horizontals.. do i need to change shtSteel to
Steel?
the other thing is you mentioned ive created a userform w/ a single combo
box? i most certainly don't know what that is. lol. so, can you explain what
im to do with this to make it work?
.... just for clarifications, Members and Steel List are ...
are they the name of my sheets?

last thing. how would i tie this to a cell? would i call something like..
if (A3>deflction max, "insert code name here",0) sort of thing?

 

[jasontferrell]

Change "Steel List" to "Steel" and "Members" to "Horizontals".
When you go into the VBA editor, (ALT+F11), you can insert a userform
by clicking the Insert menu, then UserForm. The Controls toolbox
should appear and you can hover over the combobox and it will have a
tooltip that says ComboBox (not ListBox). Click on this. Then to
insert it into the form, click once and hold it to mark the top left
corner of the combobox, then move the mouse lower and to the right and
release the button to mark the bottom right corner.

Next, right click the ComboBox and click view code. It should bring
you to a code form where you can paste this:
Private Sub ComboBox1_Change()
ActiveCell.Value = ComboBox1.Value
UserForm1.Hide
End Sub

This will put whatever value you select into the active cell.
You need to put the code I wrote above into a module, then go to the
Excel menus and click Tools->Macro->Macros. Then select the macro
"PopulateAcceptList" and click options. You can add a shortcut key
here. Use something like semicolon or something else that isn't used
already by Excel.

So, my idea would be to go to the Horizontal sheet and put make the
active cell on the line where all your data is for a particular
horizontal. Then you could use the shortcut, (CTRL+, and trigger
the code. This would open up the userform with the combobox and allow
you to select the appropriate steel for reinforcement.

You'll need to understand the code so that you can modify it to make
it work, however. Here's the code again with better comments.

Option Explicit
Public Sub PopulateAcceptList()
Dim shtSteel As Worksheet, shtTestMember As Worksheet
Dim lRow As Long
Dim lCurrent As Long
lCurrent = ActiveCell.Row
Set shtSteel = Sheets("Steel")
Set shtHorizontal = Sheets("Horizontal")
'first I'm setting variables for the two sheets since you need to
compare values on both sheets in order to determine which steels will
work.
'clear out old combobox items (we don't want steels that worked
for the last horizontal already in the box, only the ones that we
determine will work for this one)
UserForm1.ComboBox1.Clear
For lRow = 1 To shtSteel.UsedRange.Rows.Count
'calculations here to compare some calculation of the steel
to
'what would be required for the member - it may be a
combination of calculations from both sheets
'I added two variables, one from each sheet to show how this would
work
'In this case, lRow is the row that we're currently looping through in
order to determine if the steel will work
'lCurrent is the row of the horizontal that we're testing
If shtSteel.Cells(lRow, 3) > shtHorizontal.Cells(lCurrent, 4)
Then
'this steel works - if it works, we add it to the combobox
so that you can select it
UserForm1.ComboBox1.AddItem shtSteel.Cells(lRow, 1).Value
End If
Next lRow
'after looping through all the possible steels and adding the ones
that work, we display
'the form with the combobox
UserForm1.Show
End Sub