Next blank cell (and then the next. . .)

[Nick Vittum]

Here I am again. I thought I'd give Hans and snb a rest (and anyone else who looks at my convoluted threads. Thought maybe, just maybe, I could figure this out on my own, or adapt code snippets from elsewhere. Or failing those, at least bother someone else for help. Big Fail. I've been at this all day and gotten nowhere

Background: This workbook is for managing my finances. Sheets are initially filled with data from the Bank; then the primary Userform is used to categorize expenditures in Column B. Because the Bank data is often in a "code" all its own, it's sometimes difficult to tell at a glance what those expenditures actually were. In these cases, I need to refer to other resources to figure this out. In these cases (especially if 'other resources' means the website of someone I do business with) it's much more efficient to browse down through the worksheet and do all expenditures from that particular vendor at once—which means leaving other blanks behind till later. Hence I'm trying to set up a button that will "skip" down through the column. I didn't realize how important this was for speed until I started trying to really use the workbook instead of just experimenting with it.

I've tried out umpteen-jillion versions of code that will find the first blank in the column, or the last. But none that will let me browse and enter data in a non-linear fashion, and none that I could find a way to adapt. Then I thought maybe I was making it too complicated; maybe I should just use the VBA Find function. But again, it would only find the first blank cell, and wouldn't budge from there. I couldn't learn how to incorporate FindNext into the routine. (Also was afraid it would try to continue ad infinitum, past the UsedRange—but I never got far enough to reach that problem.)

The most hopeful thing I found was offered to me from someone elsewhere—

Code: Select all

Private Sub NextBlank()   
Static NxtCell As Range
   If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count)
   Set NxtCell = Range("B:B").Find("", NxtCell, , , xlByRows, xlNext, , , False)
   NxtCell.Select
End Sub

But the behavior of this code was erratic at best. Among other things, it retains a 'memory' of where I left off. So if I go back up the sheet to fill in a bit of data I just found (or start on the next vendor) it remembers where it left off, and so jumps to there, instead of starting again from the currently active cell. (that person got tired of me—understandably)

I ideally, I'd have code that would start from cell B2 by default— But what's really important, and all that really matters, is it it would use whatever B-cell I've currently activated as a starting point, and move on from there.

I thought this was going to be so simple. . .

[HansV]

The code that you posted should work unless you reset the NxtCell variable by closing the userform, or clicking the Reset button in the Visual Basic Editor, or using the End command, or if an error occurs.
I'd move the NextBlank procedure from the userform module to a standard module, and remove the keyword Private.
See if it works more consistently.

[snb]

1 general comment: 2 times 'searching' is equivalent to f'filtering'. The find method is not fit for filtering.

Check this:

Code: Select all

Sub M_snb()
   for each it in sheet1.columns(2).specialcells(4)
     msgbox it.address
   next
End Sub

In this case you use a collection of specialcells; these are indeed very special cells, because they are empty.

Later I can show you the filtermethods autofilter & advancedfilter.

[Nick Vittum]

The code that you posted should work . . .

I'm near despair over this. I do know if should work; but it simply doesn't. I may not have been clear enough about the use of this button and the problem I'm encountering with it, so let me describe a typical situation:

Suppose I'm going through last month's sheet. The bank data has been imported, and I've made my first quick pass down Column B, applying categories to all the expenditures that are obvious from the bank's information (this, by using Combo_categories, which is bound, if that's the right term, to Column B). After that, 10 items have been left blank because of insufficient information. 6 of those items are from Amazon, because I buy many dry goods and household items there (especially in these pandemic days). Amazon's order information is almost as abstruse as the bank's, so I spend time on their site analyzing order history. As I do, I work my way down Column B a second time, entering categories for each order. I use the Next Blank button to make this second passage as quick as possible, skipping through to find the Amazon entries. When I reach the last empty cell, I press Next Blank once again, and it takes me to row 39, where there is no data, and I know I've reached the end of the Used Range. Now Amazon is complete; still 4 unknown items to categorize. I go back to the top (B2), and press Next Blank to start again—and where does it take me? back to row 39, skipping all the blanks that I previously skipped over.

In short, it somehow retains its own memory of where it left off, and won't go anywhere else. Nothing—not even unloading the form—will clear that memory. And I do mean unloading it, not hiding it. The only way to clear it is to open the VBA editor and reset it there, or close the workbook and reopen it. Needless to say, neither option is practical—at that point I might as well be working directly on the sheet and not even have the Userform.

I can't imagine what makes it hold on to that memory, but I'm assuming that's "just the way it is" with Excel. And if that's the case—and unless there's a way to clear that memory with code—then I need different code that will use the last cell I selected as its reference point to jump from, rather than its memory of the last cell it selected. That one key element is what I spent the whole day searching the web for yesterday, trying out every bit that seemed hopeful. I couldn't find a single place that addressed the problem directly, or even a mention of anyone else ever encountering the issue. Which has led me to think it most be something so simple and obvious that no one has even bothered to write about it.

Just to make sure it wasn't something weird with my code-heavy workbook, I tried it out in the little workbook we were using the other day for duplicates, and got the same result. I've attached it, if you're willing to take a look at it.

Thanks again

edit: Sorry. Here's the attachment now.

[snb]

You have attached what ??

[Nick Vittum]

[1 general comment: 2 times 'searching' is equivalent to f'filtering'. The find method is not fit for filtering.

Thank you for letting me know that. At least one wild goose I don't need to be chasing

Code: Select all

Sub M_snb()
   for each it in sheet1.columns(2).specialcells(4)
     msgbox it.address
   next
End Sub

Your code is, as always, wonderfully brief and concise. But sadly, it exhibits exactly the same behavior I described in my post to Hans. That is to say, if I replace the message box line with

Code: Select all

    it.select

If I used exactly as you posted it, with the message box, it wouldn't even let me out of the routine to edit the blanks as it found them.
Also, as a side note, Excel would not accept "it." I got away with it by removing Option Explicit; but in the future, how should "it" be declared? And what is it?

Later I can show you the filtermethods autofilter & advancedfilter.

Excellent. Thank you.

[HansV]

Does this work better for you?

Code: Select all

Sub NextBlank()
    Static NxtCell As Range
    If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count)
    Set NxtCell = Range("B:B").Find("", Range("B" & ActiveCell.Row), , , xlByRows, xlNext, , , False)
    NxtCell.Select
End Sub

[HansV]

snb never declares variables.
"it" in his code is a Range, so you could use

Code: Select all

    Dim it As Range

[Nick Vittum]

Does this work better for you?

Code: Select all

Sub NextBlank()
    Static NxtCell As Range
    If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count)
    Set NxtCell = Range("B:B").Find("", Range("B" & ActiveCell.Row), , , xlByRows, xlNext, , , False)
    NxtCell.Select
End Sub

Oh, wonderful! Yes, it works! thank you so much.
(I'm almost certain there was a line like your new one on once of the many versions I tried yesterday. I can't bear to go back looking through them now. But in any case, obviously somehting else must have been different.) I was feeling pretty sad and frustrated. You made my day./

[HansV]

You can leave out the line

Code: Select all

    If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count)

It isn't needed any more.

[Nick Vittum]

You can leave out the line

Code: Select all

    If NxtCell Is Nothing Then Set NxtCell = Range("B" & Rows.Count)

It isn't needed any more.

Thank you. :-)