Find Next Function with two criteria Help

Hello All,


First of all sorry if this is answered somewhere already, but I didn't know exactly how to word it.

I am an Engineering Intern that has somehow been tasked with writing Excel Macro for the last month. I've taught myself quite a lot so far but cant figure this one out.

The first part of the macro needs to search Column Z for the next cell that is not blank or contain a value of 12 in it and then select it. (I.E. a cell contain a value of 8 or 4.2)

The next part of the macro needs run a goal seek function changing column X until Column Z is 12

Then End itself when all of Column Z is either blank or 12


I have written This code so far and it works perfectly except it checks every single row. Which is super slow since there are currently 75500 rows at the moment and will only increase over the years.

Please Login or Register  to view this content.

My theory for writing it like this was to hopefully speed up the checking process before goal seek was used. but it is still very slow. ( I know the goal seek part will have to be slow no matter what)

This code runs when the file is saved and that will only happen if someone updates a value which will change minimum 27 rows and maximum 2145 rows, So the bulk of the 75500 rows will not need to be looked at before saving. (Which I can't seem to avoid)

Any help or lessons would be greatly appreciated, I'm a quick study most of the time, but I can't find any info on this one to study unfortunately.

Try this...adding Application.ScreenUpdating = True and Application.ScreenUpdating = False like so...

Please Login or Register  to view this content.

It might help a little bit.
I have a question though, you have a If Then statement, but after the Then it goes straight to Else. Usually there is something in between.

Wow, over 6 hours! I'm not sure I know of a way to make it quicker. Hopefully someone else with more knowledge will have a solution. Oddly though, I would think that with only 75500 checks it wouldn't take that long at all. Have you tried adding Exit Sub after Then and before Else? Hmm....maybe change your code to this instead....

Please Login or Register  to view this content.

Edit* Not sure if this code will work at all...in my head it works perfectly.

Something with that Then Else just isn't sitting right with me. Did you try adding Exit Sub after the Then and Before Else? Normally, at least from what I have seen, with an If Then, there is always something after the then and before else. It this Then do this, Else do that. The way yours is written its doing If Then Else, and has me baffled.

Edit*Actually, I've gotten myself so mixed up I'm not even sure adding the Exit Sub is correct.

This code looks better and does the same I think:

Please Login or Register  to view this content.