keeping track of possible solutions?

**oks10** · 04-23-2010, 04:45 PM

Hello all, first post.

I'm working on a VBA program for a design project and have hit a bump in the process. I have a For...Next that searches through a spring catalog database and finds springs that fit withing a certain diameter. After it finds a spring that fits it determines if it provides a required spring force, the rest is irrelevant to this question. My problem is that as the code stands now as soon as 1 spring is found that fits, it outputs the information of the spring and stops. What I really need to do is have it run through all the possibilities (which I can already do) and keep track of all the springs that fit the requirements so I can have it output the BEST spring not the FIRST it finds. Any suggestions about how I can accomplish this without needing to use a bunch of variables to assign the values to? This project is for a private company project and I don't feel comfortable posting the code I have so far.

Sorry for the wordy post but any help is appreciated, example code isn't really necessary just trying to figure out what process I can use. Thank in advance for the help.

**davegugg** · 04-23-2010, 05:10 PM

Sounds like you have an IF statement which tracks if a set of criteria are met. Once they are met, the code outputs the parameters.
You need to assign variables to record the best parameters, then output that at the end once all records have been examined. You would need variables to hold the best parameters thus far, and compare these to the current record's parameters.

This will be easiest if you have an index number (like the spring's serial number?) for each spring. Then you can record the best (so far) spring's index and output that index's spring at the end.

Hope that makes sense. If you want more detail but don't feel comfortable posting your code, you could try posting some psuedo-code, and we could try to work with that.

**oks10** · 04-23-2010, 05:27 PM

Yeah, it's several IF statements inside a giant For Next. That makes sense what you said, It would definitely be the simplest way to code what I'm needing. I just need to decide what is the most important parameter I want to base the selection process from. I think I should be able to move on easily with this. I was just having trouble thinking because this code has consumed my mind the past week. Just had to step back from it.

Also, I'm having an issue with a particular row of cells and when the calculations are ran for it, there is an overflow due to dividing by zero because a parameter is missing. In an attempt to skip the row, I tried putting an IF statement in that was basically

Please Login or Register  to view this content.

but it gave me a "Next without For" error.

Does this make sense what I'm trying to do? I didn't know if it would work but I gave it a try and failed.

**davegugg** · 04-23-2010, 05:36 PM

I would not skip the row, I'd test the parameter's cell for 0, and skip if it is 0.
Regardless, the error is occuring because you can't have the Next R being conditional based on the if. Instead, use a Goto and place the target just above your Next R in the code. Make sense?

**oks10** · 04-23-2010, 05:44 PM

I think so. I'll try it out on my own and come back if I run into trouble. I'll actually learn something that way instead of just asking someone to step me through it. Thanks a lot for the help again.

Edit: The GoTo command worked perfect. Thanks.