Macro to jump to next blank with a criteria

[MannStewart]

hi

I have a form button that I need the cursor to jump to the next blank in the column BP9:BP, where the first data row starts from BP9, and user inputs data non-contiguously in column BP9:BP (column 68). The mandatorily contiguous input range "Date" column D determines last working row. The find criteria is to jump to the NEXT BLANK in BP9:BP which ALSO is accompanied with a FILLED NUMERIC VALUE in its adjacent column BO (on same row with BP). For example, some BP9:BP may be blank but its adjacent BO could be filled with a TEXT value, or BO could be just empty, (these types of blank BP9:BP should just be skipped and not to be bothered with by the macro). BO9:BO is auto-calculated by formula, currently has formulae (that isn't dependent on BP), down to row 10,000, & on workbook open auto-calculation if D is filled, it returns a value that may be TEXT or NUMERIC, or returns a blank.

The find criteria is only above, go for the next BP blank starting from BP9, stop at the next BP that is blank, that has an adjacent BO that is filled & BO is a NUMERIC value.
I have an existing code that does a simpler criteria by finding for the next blank in AW9:AW as follows but I didn't have the capability to adapt this code (i think it's the blue bit) to fit the above criteria. If anyone can show me how the modification should be done, I would appreciate it.

Please Login or Register  to view this content.

cheers
Stewart

[ByteMarks]

Maybe something like this?

Please Login or Register  to view this content.

[MannStewart]

hi there

so i tried your code, but it stops only at the first blank and won't move anymore, and it also displays the msgbox at the same time too that there are "No matching blanks", when it shouldn't because there are still blanks to be found below. Maybe somewhere along the 4th paragraph needs rearranging ?

Stewart

[ByteMarks]

Hi, Sorry the message box was a mistake.

How about this?

It looks for blanks in the column BP where BO has a number in it.

Please Login or Register  to view this content.

[MannStewart]

hi

it performs erratically this time.

This is the peculiar situation:

at first run, it seemed to work, jumping from 1st blank to next & onto the next till the last, but i noticed you added the msgbox " ... Reset ?" After that it didn't work anymore. It just jumps straight to the last blank that meets the criteria at the last row of the sheet, bypassing all other blanks that also meet the criteria above it. Once user goes on working with inputting the sheet, and at a later point goes back to click it again, it won't work Maybe it's better off without the Reset question, and just display the msgbox "No more unrecon" instead ?

I don't know if this msgbox is an apt idea, because there is no need to ask the user, i think the best is to do without it. To test if it was due to choosing No previously, i tried removing the section with the reset msgbox, at 1st run it worked again, but when repeating the exercise it didn't work anymore just like previously when it was with the Reset msgbox. So, i think it's not the msgbox that failed it. What's the reason for this ?

[ByteMarks]

Yes you could get rid of the question.

Please Login or Register  to view this content.

[MannStewart]

hi

it's still the same, despite without the question.

At first run, it works, till the last blank. After that, it will not work anymore. It just always jumps straight to the last blank thereafter.

Perhaps it might be a structural issue with the code? although i'm no expert with codes. I'm thinking, the code needs to go back to the start after reaching the last blank BP that meets criteria? I mean, it has to allow user to keep repeating (START ALL OVER AGAIN) the process every time it has finished running through the range BP9:BP lastrow

[ByteMarks]

it has to allow user to keep repeating (START ALL OVER AGAIN) the process every time it has finished running through the range BP9:BP lastrow

It is doing this for me.
Perhaps try declaring rngLastFoundBlank at the module level?

Please Login or Register  to view this content.

[MannStewart]

if i did accordingly as you suggested, it wouldn't run after deleting

Please Login or Register  to view this content.

even with

Please Login or Register  to view this content.

separately mentioned

I don't know how it is working for you, do you mean you can do it over & over again after reaching the last blank BP that meets criteria? If after you reached the end, then if you put your activecell somewhere on the sheet, and you clicked the macro again, it would start at the first blank BP again ?

[ByteMarks]

do you mean you can do it over & over again after reaching the last blank BP that meets criteria? If after you reached the end, then if you put your activecell somewhere on the sheet, and you clicked the macro again, it would start at the first blank BP again ?

Yes that's what happens for me. Maybe you could post your workbook, that might help identify the issue?

[MannStewart]

That's awfully strange then / i wish i could but it's too big a file

I've kept retrying many times with your code but still same result, i don't know how to go from here.


Is there any way you can help me modify the code (essentially the blue bit) i first posted because that code worked for my workbook, only the part for the criteria needed tweaking
I'm hoping at least I can have something that can help to meet ongoing work and get through the week. Kindly see if you can help ?

Stewart

[ByteMarks]

Please try this.

Please Login or Register  to view this content.

[MannStewart]

hi

.. that did it


cheers!

[ByteMarks]

That's great.

[MannStewart]

hi

been using for awhile and only discovered recently an issue

if the user has searched & then leaves the Sheet to activate another Sheet, then returns to the Sheet to continue searching, the macro should start afresh from new at the top of the BP9. But, the current macro continues where it left off when the user left the Sheet. This creates a host of issues with the normal work. Can you help me revise the code so that every time the user returns from another Sheet and clicks on the macro, the macro would start as new and NOT continue where it left off? I tried adding a Range("A1").Select to Activate.Sheet thinking this might resolve the issue and make the search start anew, but it doesn't.


Stewart

[ByteMarks]

Hi,

The static variable retains the last position. Easiest option is to declare it at the worksheet level and then reset it in the deactivate event.

Please Login or Register  to view this content.

[MannStewart]

so, which variable(s) is/are static ? would that be all these:

Please Login or Register  to view this content.

..and,

how to reset them if create a macro on Deactivate Sheet ?

[Trebor76]

You could also use this event macro* on the tab in question:

Please Login or Register  to view this content.

Regards,

Robert

* Follow these four steps to put the above code on the desired tab:

1. Copy my code to the clipboard (Ctrl + C)
2. Right click on the relevant tab name and from the shortcut menu select View Code
3. Paste (Ctrl + V) my code you copied to the clipboard from step 1
4. From the File menu click Close and Return to Microsoft Excel

[MannStewart]

hi Robert

Great to hear from you. But what you suggested was 1 of the things i had first tried (days ago), & it didn't resolve the issue. I even tried putting that on the top of the code other than the Sheet. No change

I don't have a deep command of VBA but maybe the macro really is hinged on the variables like what ByteMarks said. So, maybe the solution is to reposition / reconfigure the code about how the variables can be released from the last address no matter which Sheet the user chooses to jump to & fro between Sheets?

Stewart

[Trebor76]

Though clunky try this which I have based on my solution (post #15) to your original thread from here:

Please Login or Register  to view this content.

Regards,

Robert

[MannStewart]

hi Robert

i've copied & applied your code above, but for some reason it doesn't work & on click just jumps straight to cell DV6 and stays there forever.. ie. further clicks won't move it (DV6 is not locked, so that may be why your macro consequently landed there, but why it chose to go there i don't know)

(sorry about previous wrong message it was Bytemark's code that is working just not going to the top as I needed, I was switching your code and his for the button to compare & find the reason why your code isn't working)


Stewart

[ByteMarks]

so, which variable(s) is/are static ?

The one declared as Static in the original code.

Please Login or Register  to view this content.

Post 16 has the modifications to make the search start from the top.

[jindon]

The find criteria is only above, go for the next BP blank starting from BP9, stop at the next BP that is blank, that has an adjacent BO that is filled & BO is a NUMERIC value.

Please Login or Register  to view this content.

[MannStewart]

hi Bytemarks,

should i just overwrite the entire original code with the code in post#16 ?

Stewart

[ByteMarks]

should i just overwrite the entire original code with the code in post#16

Yes please.

[MannStewart]

hi Jindon

So good to see you here, and I must say your code at first sight strikes me as quite economical, however what it does is just lands on the first cell in BP that meets the criteria and won't go down further where there are plenty more cells that do. Elaboration on your code might , maybe..?

Stewart

[jindon]

Do you want all the matched cells? not only the first available cell?

[MannStewart]

Do you want all the matched cells? not only the first available cell?

yes, i need it to go to each & every that meets on each click

[jindon]

Each click of what?

[MannStewart]

hi ByteMarks

but it cannot run because in post#16 you gave me a

Please Login or Register  to view this content.

if I assign the macro to ActivateNextBlank with a Dim on the top EXCEL says ONLY COMMENTS MAY APPEAR AFTER END SUB, END FUNCTION OR END PROPERTY.

or i must be doing something wrong to overwirte the original code, if you could explain to me how to correctly again ..

Stewart

[MannStewart]

Each click of what?

So, this macro is for a form button. I need with each click of this button i need to send the activecell to the NEXT cell in the range BP below the current one, that meets the search criteria.

the criteria is simple: the next cell in BP that is blank, and its adjacent BO has a numeric data (BO contains TEXTs, NUMERICs, blanks)

Stewart

[jindon]

See if this is how you wantd.

Please Login or Register  to view this content.

[MannStewart]

See if this is how you wantd.

Please Login or Register  to view this content.

wow ... so short yet yes it does

ok, it essentially is doing what it's supposed to, only 2 issues left, that is, your MSGBOX doesn't seem to be working when last reached, and that issue that i would like it NOT to still dwell in the last searched location when user has left the Sheet for another, ie, i need the search to start afresh from the top of BP9 when user returns to main Sheet from another Sheet

Stewart

[jindon]

MSGBOX is not working

This doesn't help.
How is it not working?

[MannStewart]

This doesn't help.
How is it not working?

maybe i thought your MSGBOX was for a warning to user when last criteria-satisfied cell is found, i mean if your MSGBOX is for when no cell meets criteria then it's fine. If i need a MSGBOX for warning to user when last criteria-satisfied cell is found, where should that be inserted?

[jindon]

To the worksheet code module.

Please Login or Register  to view this content.

[MannStewart]

hi Jindon

the addition of the MSGBOX is good now, but that

Please Login or Register  to view this content.

is causing a line in my Private Sub Worksheet_Change(ByVal Target As Range)

specifically, the line

Please Login or Register  to view this content.

to malfunction with an Error message COMPILE ERROR VARIABLE NOT DEFINED. The Worksheet Change was working fine all along even without defining it (before adding your above), now how should I work around this after adding..? After adding the Option Explicit, now every time i make any input change to the Sheet, this error pops up.

Even as I define it as Long, EXCEL looks for other variables (1-by-1) to call out the same Error msg complaining they are not defined, and I have quite a number of variables that were working nicely without being defined.
Any workaround ..?

[jindon]

Ahh, I didn't read it till the end.

Add one line in you Worksheet_Change code

Please Login or Register  to view this content.

[MannStewart]

Ahh, I didn't read it till the end.

Add one line in you Worksheet_Change code

Please Login or Register  to view this content.

yea, i did that like i mentioned, even as I define it as LONG under Worksheet.Change, EXCEL would just pick out other variables (1-by-1) to call out the same Error msg complaining they are not defined, and I have quite a number of variables that were working nicely without being defined. Did you mean there's another place i should define or there's no way ..? And, just supposing that I do exhaustively define ALL variables that exist on the main Sheet, would that make the error go away completely ? And should every variable be defined as LONG?

[jindon]

Can you post your code?

[MannStewart]

Can you post your code?

i tried but i don;t know if it's because the code is too long (amateurishly long) mainly because it consists of many bits & parts i put together myself that i couldn't submit it here. It just gets stuck in Preview although i clicked Submit, why is this

Is it alright if i PM you the code ?

And, would the EXCEL UNDEFINED VARIABLE error also affect all other codes under other sheets if I went ahead with your Private n as Long ?

[jindon]

Or try declare every variable whatever the variable type, just

Please Login or Register  to view this content.

If you declare all the variables without specific data type, they are all Variant.
It is fine as long as the variable used are declared within the procedure.

[MannStewart]

Or try declare every variable whatever the variable type, just

Please Login or Register  to view this content.

If you declare all the variables without specific data type, they are all Variant.
It is fine as long as the variable used are declared within the procedure.

should i just define all Variables as LONG? only for the variables that appear under the Main Sheet code right ?

[jindon]

No, just like I said, no data types.

[Trebor76]

i've copied & applied your code above, but for some reason it doesn't work & on click just jumps straight to cell DV6 and stays there

I can't see how that could happen as nowhere in the code do I tell it to land anywhere in Col. DV

I see jindon has taken up cause so you are getting some of the best support possible so I'll humbly bow out at this stage.

Good luck with it.

Robert

[jindon]

If you can not post your code, you can upload a workbook.

[MannStewart]

I can't see how that could happen as nowhere in the code do I tell it to land anywhere in Col. DV

I see jindon has taken up cause so you are getting some of the best support possible so I'll humbly bow out at this stage.

Good luck with it.

Robert

You're instrumental Robert, my first button still relying on your code.

cheers
Stewart

[MannStewart]

If you can not post your code, you can upload a workbook.

my actual workbook is even larger, i'm going to try to declare the variables like you suggested first and then i'll try to post the code again

[jindon]

All I need to see is the code, so no need of big file.

Anyway, I will shutdown my pc soon, so maybe tomorrow.

[MannStewart]

hi Jindon

the EXCEL UNDEFINED VARIABLE error has completely gone after declaring all variables.

Now, after adding

Please Login or Register  to view this content.

the difference is this:

it still doesn't go to the top of range BP9 when user comes back to Main Sheet,
AND
the macro skips 1 cell that meets criteria unlike before adding above code to SheetActivate where this never happens

I can see although you set n = 0, but it seems somehow is that causing the skipping..?

Stewart

[jindon]

No idea with just a part of the code that you are using.

It is working properly here.

I can not help more without seeing your workbook.

[MannStewart]

No idea with just a part of the code that you are using.

It is working properly here.

I can not help more without seeing your workbook.

here is the Main Sheet Activate code

Please Login or Register  to view this content.

[jindon]

And where is the "test" sub procedure?

[MannStewart]

i put it in a separate module, exactly as your code because it's for a button

[jindon]

NO, it MUST be placed in the same WORKSHEET CODE MODULE.
I said,

To the worksheet code module.

[MannStewart]

NO, it MUST be placed in the same WORKSHEET CODE MODULE.
I said,

ok, i see it, there's a "Sheet1" preceding the macro name, i didn't knowi can place like this as I never tried this way before. let me try and see if this works this time

[jindon]

If your Sheet Code Name is Sheet1, it should appear as Sheet1.test when you assign it to a Button.

[MannStewart]

the skipping has gone

but it still won't go to the range top to start the search when returned to Sheet

My workbook is set to manual mode calculation as there are too many formulae and sheets, is that a factor ?

[jindon]

It is just wasting time.

Let's stop here, if you don't upload a workbook.

[MannStewart]

Jindon, as i explained earlier my workbook is huge not to mention the fact that i can't really post the school file.

I appreciate all the help you have offered thus far nonetheless.
As far as this issue goes, i'll try to figure out why it's not working in my file, since it's working for you.

cheers,
Stewart