VBA help: how to set dynamic next available row

[Lil Stinker]

I'm new to VBA so please forgive me if this is something completely basic. I currently have a worksheet set up where I want to add notes to the sheet via a userform entry. The notes are entered to the sheet row after row, one note/row at a time. What I am trying to have it do is enter the first note in B2, then B3, then B4, so on and so on until it reaches B10. At that point, I want it to redirect to the next open column and start at K2, K3, etc. down to K10. Then if K10 is not empty, change the next available row to B20, down to B30. Once B30 is filled, change again back to K20.

The reason for this is the worksheet is designed a certain way. Row B1 has a header that is Centered Across Selection. Rows A11 through A18 also have other fields that shouldn't be overwritten while B19 has another header Centered Across Selection.

I know how to the get the last row available with the code noteROW = Sheet1.Range("B11").End(xlUp).Row + 1 however, when I change noteROW to equal Sheet1.Range("K11").End(xlUp).Row + 1, it doesn't recognize the header in B1:S1 and places the note in K1 instead of K2. And for reasons unknown to me, when K10 is not empty and I change noteROW to Sheet1.Range("B31").End(xlUp).Row + 1, the notes don't even show up.

Here is the code I've tried working with:

Private Sub formsaveBTTN_Click()
   Dim noteROW As Long

If scanBOX.Value <> "" And notesBOX.Value <> "" Then
      With RTNreceipt
         noteROW = .Range("B11").End(xlUp).Row + 1 'first avail row
         .Range("B" & noteROW).Value = scanBOX.Value & " - " & notesBOX.Value 'insert barcode and equip notes
         If .Range("B10").Value <> Empty Then
            noteROW = .Range("K11").End(xlUp).Row + 1 'first avail row in next column
            .Range("K" & noteROW).Value = scanBOX.Value & " - " & notesBOX.Value 'insert barcode and equip notes
         End If
         If .Range("B10").Value <> Empty And .Range("K10").Value <> Empty Then
            noteROW = .Range("B31").End(xlUp) + 1 'first avail row on pg2 of notes
            .Range("B" & noteROW).Value = scanBOX.Value & " - " & notesBOX.Value 'insert barcode and equip notes
         End If
         If .Range("B30").Value <> Empty Then
            noteROW = .Range("K31").End(xlUp).Row + 1 'first avail row in next column
            .Range("K" & noteROW).Value = scanBOX.Value & " - " & notesBOX.Value 'insert barcode and equip notes
         End If
         If .Range("K30").Value <> Empty Then
            MsgBox "****! How much more damage could there be???", vbExclamation, "Out of room!"
         End If
End If

Attached is a screenshot of the worksheet layout which displays the desired results, not the results I've been getting.

I'm sure there are much more sophisticated ways of coding this method. I'm pretty much limited to If/Else statements right now so I'd appreciate any advice!

 

[skillilea]

Do you have flexibility in the spreadsheet layout?

I'd really recommend putting your notes in a table. Always adding the most recent note to the top ie (sorted by date entered).

This layout is ripe for issues.

You would have a difficult time finding any specific note this way, ie searching by date, person etc.

 

[Lil Stinker]

Do you have flexibility in the spreadsheet layout?

I'd really recommend putting your notes in a table. Always adding the most recent note to the top ie (sorted by date entered).

This layout is ripe for issues.

You would have a difficult time finding any specific note this way, ie searching by date, person etc.

Thanks for the reply. No flexibility in the layout really. The layout is set up this way specifically for printing. The notes are backed up to another worksheet to handle searches and filtering when needed. I've got a whole system set up, this is just one tiny aspect of it. I'm just looking for a way now to have the notes the users enter to be printed on the receipts that are generated when checking in equipment.

 

[skillilea]

this may work for you.

Get rid of the notetext and inputbox.

Public Sub AddNote_Click()
    Dim ws As Worksheet
    Dim noteText As String  ' delete this
    Dim rng As Range
    
    noteText = InputBox("Enter your note:", "Add Note", "test") ' delete this, use your form  ' delete this

    Set ws = ThisWorkbook.Sheets("Sheet2") ' your sheet
    
    Set rng = FindNextBlankCell(ws)
    
    If Not rng Is Nothing Then
        rng.Value = noteText   ' use your form.value here
    Else
        MsgBox "No more cells to fill" ' ran out of cells
    End If
End Sub

Function FindNextBlankCell(ws As Worksheet) As Range
    Dim rangeItem As Variant, fillRanges As Variant
    Dim cell As Range
    
    fillRanges = Array("B2:B10", "K2:K10", "B20:B30", "K20:K30")

    For Each rangeItem In fillRanges
        For Each cell In ws.Range(rangeItem)
            If Len(cell.Value) = 0 Then
                Set FindNextBlankCell = cell
                Exit Function
            End If
        Next
    Next
    Set FindNextBlankCell = Nothing
End Function

 

[Lil Stinker]

Thanks for this, I will give it a try when I'm back in the office on Monday and let you know.
In the meantime, I currently have "AddNote_Click()" as a Private Sub saved in my Userform. Should I change it to Public as you have it?
As for the Function part, should that be in a separate module outside of the Userform?

Sorry, total noob here.

 

[skillilea]

No worries they can both be private inside the userform.

 

[Lil Stinker]

This seems to work perfectly in my tests!
Now here's where I'm going to get dinged for not sticking to the rules. I didn't think to mention beforehand that I also have a button on the userform for clearing any notes in case a mistake was made or whatever. Each note that is generated on the form is preceded by a unique serial number, followed by the note detail. Example, "1234 - liquid damaged". Is there an easy way of adding in a clear option to this sub based on the unique serial number? Or is this where you meant "the layout is ripe with issues" and this would be a difficult addition?

 

[skillilea]

Just to be clear. You want to go find "1234 -" liquid damaged" and clear the liquid damaged part or the whole cell contents.

Does the current button clear the active cell?

Assuming you mean the worksheet when you mention form, correct?

 

[NoSparks]

I think a more simplistic approach would to be Union the ranges.
Because they are single column ranges a For-Each loop will step through the cells in the required order.

Sub formsaveBTTN_Click()

Dim rng As Range, cel As Range, noteCell As Range

If scanBOX.Value <> "" And notesBOX.Value <> "" Then
    With RTNreceipt       '<---- sheet code name
        Set rng = Union(.Range("B2:B10"), .Range("K2:K10"), .Range("B20:B30"), .Range("K20:K30"))
        For Each cel In rng
            If IsEmpty(cel) Then
                Set noteCell = cel
                Exit For
            End If
        Next cel
        
        If Not cel Is Nothing Then
            noteCell.Value = scanBOX.Value & " - " & notesBOX.Value
        Else
            MsgBox "****! How much more damage could there be???", vbExclamation, "Out of room!"
        End If
    End With
End If

End Sub

Can use the same approach to find the serial for clearing.
Clear the cell and a run of this macro again will write to the just cleared cell.

 

[Lil Stinker]

Just to be clear. You want to go find "1234 -" liquid damaged" and clear the liquid damaged part or the whole cell contents.

Does the current button clear the active cell?

Assuming you mean the worksheet when you mention form, correct?

The entire note in each cell is formatted like "1234 - liquid damage" so we would clear the whole cell contents.
Sorry, I meant form as in userform because the note is entered into a textbox on the userform and then saved to the worksheet when the user hits the Save Notes button on the userform. There is a textbox named scanBOX which the unique serial number is entered first, a noteBOX textbox where any notes are entered and when they hit the Save Notes button, the "noteTEXT" is the combination of scanBOX.Value & " - " & noteBOX.Value.

Currently, I'm using .Find to locate the serial number and clear the worksheet cell it's located in. However, with this new layout, I've had to merge the cells together in order to restrict any notes to the perceived column border on the worksheet. Each note row on the worksheet is a merged unit of 9 small columns in one (due to areas above the notes section that require 9 individual columns). Naturally, when I run my current Clear code, I now get the "Can't do that to a merged cell" error.

Your code seems to work regardless of the cells being merged which is why I was wondering if there was a way to reverse it and clear the notes on the worksheet with similar code since the cells are merged.

This is what I currently have:

Private Sub eraseNOTES_Click()
   Dim noteRNG As Range, cell As Range
   Dim barCODE As String
   
   barCODE = scanBOX.Value
   Set noteRNG = RTNreceipt.Range("B2:K10,B20:K30").Find(barCODE, , xlValues, xlPart, , xlPrevious, False)
   
   If MsgBox("Clear notes from Receipt?", vbYesNo, "Eraser Tool") = vbYes Then
      If Not noteRNG Is Nothing Then Cells(noteRNG.Row, noteRNG.Column).ClearContents
   End If
End Sub