Compare cells, If cell is blank move to next cell, ect

Greetings all and thanks for taking the time to look at my "little" problem.

So, overall, I am not the most experienced person at VBA, and especially when it comes to Excel, am quite rusty. Thus I find myself in a situation where I need to do something that I most certainly know is possible, but can't seem to make it happen. Well, I might have made it happen in one of my more interesting subs that caused Excel to implode... but we will never really know if it "worked".

I would appreciate if beyond just throwing out "the right code", you could also help by showing your thought process or why it should/could/will work. Ultimately while I need a working answer for this one situation, I would also like to come away alittle smarter for the future.

So now we are at the fun part. What am I trying to do?

Well for starters I am using Excel 2007 on both XP and 8.1.

Essentially I have a Cell that needs to compare itself to another Cell earlier in the spreadsheet from it when a value is entered. If that cell is greater than or equal to, or less than, I need it to write/change the Cell below it. However if the earlier cell is empty, I need it to check against an even earlier cell, and so-on until it reaches a particular cell. If THAT cell is empty then it compares itself to a fixed cell.

The values won't be the same, so I can't expect any (including the fixed cell) to have a definite value. The entered value could be anything from 1 to 999; though it WILL always be numbers.

Some tricks are that the "range" (not really a range, I don't think) start at Column F and Row 30 and go to Column DA and Row 246. The cells being compared are only going to be in one column (so something in F will only every be compared to F), and they will always be 8 apart - except the fixed cell.

So essentially I need the value in 246, F to compare itself to 238, F. If that is >/</= I need it to write to 247,F. If 238, F is blank however, I need it to compare 246, F to 230 F. If that is >/</= I need it to write to 247, F. If that blank it needs to compare 246, F to 222, F... and so on and so on.

Until.

It compares 246, F to 22, F. If that is >/</= I need it to write to 247, F. If that is blank then it just needs to compare 246, F to 15, F. 15, F will always have a value.

That is the easiest example I could think of, but it needs to start from the last place the data was entered. So, for example, I won't always be starting at 246, F. I may be starting at 94, F and need that to compare back the same way.

So - what do I have so far?

Please Login or Register  to view this content.

So all this does right now, is compare whatever cell in the range I am using and whatever column I am using, to the one 8 cells before it; with a special case for 22 and 15.

This also, right now, creates a beastly amount of lag - though I have it set to only run when one of the target rows is interacted with. So when using the plentiful other fields those functions run smooth and undisturbed, and the 5 seconds of lag is only when entering a new weight.

I suppose that is another thing; showing the code pretty much sets what the main goal is. I essentially have 100 things that are being weighed at irregular intervals over 30 days, and need to show if weight is gained or lost.

Like I said earlier, I know what I ultimately want to accomplish can be accomplished, I just seem to be chasing my tail on /how/.

The first thing I would look at is using a do...until loop rather than a for ... next so you are not looping through the entire range, only up until the point of interest.

i.e.

Please Login or Register  to view this content.

I would then change it so you are not looping through every column, just the one that has been changed. Use the worksheet_change event to get the target column, i.e.

Please Login or Register  to view this content.

an alternative would be to use a formula or something to find the cell, rather than a loop.

Originally Posted by ragulduy

I would then change it so you are not looping through every column, just the one that has been changed.

Having it only run at the changed column is brilliant and cuts the lag drastically - to the point it isn't even noticeable; feel quite stupid for not thinking of it myself.

Unfortunately, right now, the loops I build keep crashing Excel. However I think this is more my ham-handed coding than anything else, and I think I will throw a few more days at it myself before I show it up to see just where I forgot to put the screws in.

Thank you, ragulduy, for your quick response.

Well I never did get a loop working, they just perpetually crashed Excel - however I did go the formula route instead.

I made a quick little Sub that duplicated the weight values to a table in parts unknown of the worksheet, and then simply set the comparison sub to find the values in it. The first find would locate the first weight, which would be the one just entered, and use that as the starting position to find the next actual weight that it needed to compare it with.

This had the added benefit of no longer requiring a special case for Rows 15 and 22, as the spacing between rows no longer mattered.

ragulduy's clever idea of switching it to column only also helped so much with the lag that I was able to leave ScreenUpdating at default, instead of cycling it on and off.

So the final Sub looked like so;

Please Login or Register  to view this content.