Find the max value in a column and do looping

[Martin Lorentzen]

................................

[Bernie Deitrick]

Do you have headings? When the looping stops, what do you want to have done? Highlight the values used - show a message about the values chosen? Show the value that does not fit? And where is the max capacity value stored, or is it an input from the user? Anyway, looping is done like this

Please Login or Register  to view this content.

[Martin Lorentzen]

................................

[Martin Lorentzen]

Would be so cool if you could adjust the code with the new information, and then i could try to build on it myself.

Or maybe some other expert would like to help?

Have a good day!

[Bernie Deitrick]

Please Login or Register  to view this content.

[Martin Lorentzen]

Thank you Bernie, it is much appreciated.

Please Login or Register  to view this content.

Though i get an error 13 mismatch on this line, so it seems one of the variables should be another type? I tried changing it a bit, but doesn't seem to work.

[Bernie Deitrick]

Is it possible that you have blanks in your columns? This should work....

Please Login or Register  to view this content.

[Martin Lorentzen]

................................

[Bernie Deitrick]

Because the max wasn't reached.

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Martin Lorentzen]

Perfect. It works. I will change it to solved. Thanks again!

[Martin Lorentzen]

Hi. I am just coming back to this code again. The below code works perfectly, but it only executes in the active sheet and i want it to execute in all my sheets.

Is there a simple way to add that?

Please Login or Register  to view this content.

[Bernie Deitrick]

Please Login or Register  to view this content.

[Martin Lorentzen]

Perfect. You are the best!

Let's say that instead of picking the largest value, it should just pick a random (positive 0<) cell in the same range, and afterwards do the same looping. Would that need a lot of changing in the codes?

[Bernie Deitrick]

Please Login or Register  to view this content.

[Martin Lorentzen]

Hmm, this is weird. My excel goes into "not responding" every time i try to run the code?

[Bernie Deitrick]

Can you post a (sanitized if needed) version of your workbook that shows the behavior?

[Martin Lorentzen]

But I just made a new module and posted the exact same code you did in there. There is nothing else. Only in the other modules, but that shouldn't have any effect on it right? Since I am only running this one

[Martin Lorentzen]

................................

[Bernie Deitrick]

The code below works fine for me - here it is in a working workbook:

Book4.xlsm

Please Login or Register  to view this content.

[Martin Lorentzen]

Hmm that is so weird. Did you try running the code in my sample file, does it work for you there?

[Martin Lorentzen]

I think it has something to do with the max capacity. I tried copying line after line into your sheet, it could do exactly the same until i copied all of row 4 into your workbook, then it gave the same error.

So the error lies within row 4, but what it is i dont know :/

[Bernie Deitrick]

The issue was that the find needed to be forced to look at whole values

Please Login or Register  to view this content.

[Martin Lorentzen]

Thank you so much Bernie. You are so cool Setting to solved again