Search for first correct item

#1

Hi everyone,

i need input from my excel file for my dynamo script.
i’m trying to find the rebar combination which fits and is closest to but bigger then cell F12 (647).
a cell is good when the rebar combination is green in both tables.

for example:
K7(770) is the closest green cell to F12 but the combination 5xØ14 (K22) is red in the second table so i can’t pick this number.
the next cell closest to F12 is P5 (785), in the second table is combination 10xØ10 (P20) also green so this one is good.

image
image1920×1080 139 KB

how can i pick the first rebar combination which is right?

thanks already

#2

You need to do two things, first being: pair up your cells. You’ll be checking both cells at the same time. The second step is to determine what makes a cell green or red (pass or fail).

Once you have those two things you’ll just be checking to see if Value 1 and Value 2 both pass their conditions for each pair of values. The first pair to both pass is your answer.

1 Like
#3

As far as I know, I don’t believe the format of the cell comes into Dynamo with the data (like the cell color), so either you have to redo the calculations that determine if it is red or green inside Dynamo or do the whole process in Excel and import the correct item.

If you can do the formulas in Dynamo, then it becomes an ‘if’ statement with an ‘and’ condition, where it returns the value if true and a null if false. Then you take the closest number that is not a null (for example, the minimum but positive from a list of value-target) along with the rebar combination for that value.

All in all, I am not a structural guy but if you can give what the calculations you use to determine if it is red or green, this should be a pretty easy script.

1 Like
#4

Hello Bart

It is possible to get the first rebar combination in Excel
Just make a script in the Visual Basic editor off Excel
To run this script you have to click the button (or f5 in the VBA editor)
it is also possible to create for example a timer in vba so that Excel run the script every n seconds without clicking the button
see pictures:

image
image1928×1186 122 KB

image
image1920×1170 119 KB

Nico

#5

thanks everyone,

i made a extra pass and fail table. if it passes, there will be a number in the table.
it looks like this:

image
image1081×255 15.6 KB

i can find the lowest value (purple arrow) in the table but not the numbers which belong to the lowest number (blue arrows).
does anyone know how to find these two numbers?

thanks already!

#6

Hello

Did you try the visual basic code i showed?
If you post the excel file Or send a personal message
i can write it for you

Nico