Increment cell value until another cell reaches its maximum

[SMKYTXN]

I am working with a simple beam calculation that gives the maximum load on the supports. Currently I can increment the start of the load manually until I reach a maximum value. What I would like to be able to do is automatically increment the starting position of the axles to determine the max load on the center support.

I've attached the spreadsheet in question. Cell D25 is the cell I want to increment until cell Q27 reaches a maximum. The increment value doesn't have to be any smaller than 100mm.

Thanks in advance.

[PCI]

It seems that the lowest value for D25 is 0.001.
What could be the largest possible value, what ever the result in Q27 ?

[SMKYTXN]

The largest value will be the sum of D13 (Span length 1) and D19 (Span length 2), the combined length of the two spans. That will basically say the trailers have completely crossed both spans.

[PCI]

See how next code can help.
The number of steps (NBs) is 100 you can increase it but it will takes longer...!

Please Login or Register  to view this content.

[SMKYTXN]

See how next code can help.
The number of steps (NBs) is 100 you can increase it but it will takes longer...!

Thank you very much! It finds the maximum as expected, now I just have to figure out how the vba code does that. I'll dig into it and let you know if I have any questions. Thanks again.

[PCI]

I'll dig into it and let you know if I have any questions

Yes of course, but don't worry it is really simple

[SMKYTXN]

Yes of course, but don't worry it is really simple

Is it possible to output the values of D25 and Q27 as they progress through the steps so that a graph can be made to show the bell curve?

[PCI]

Is it possible to output the values of D25 and Q27 as they progress through the steps so that a graph can be made to show the bell curve?

Yes, but where would you like to stack the data ??

[SMKYTXN]

Yes, but where would you like to stack the data ??

I would say a third sheet with the data would work. Somewhere, where the user won't have to worry about it. The graph will most likely live on the second sheet as verification.

[PCI]

See next code

Please Login or Register  to view this content.

[MrShorty]

As a possible alternative, would you be allowed to use the built in Solver utility? https://support.office.com/en-us/art...B-F63E45925040 Starting from your sample file in the OP, I opened the file, called Solver, and told it to set Q27 to a maximum by changing D25. It quickly resolved to a solution of D25=26200 and Q27=481.68.

[PCI]

As a possible alternative, would you be allowed to use the built in Solver utility?

Yes but there will not have any record as you have it now in sheet data.
If you just need the first result , yes record a macro doing the job and launch it when you need.