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
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. . .