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!

 

[Lil Stinker]

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.

@NoSparks Thanks for your feedback.
I like your approach because it is similar to what I was thinking as soon as I saw @skillilea's array code. I figured there must be a way to combine the ranges but how was beyond my understanding. Both make perfect sense and both work great.

Actually, your approach is easier to follow because of its simplicity. And far be it from me to judge but I noticed when running your code vs. skillilea's, just in terms of speed, in order to copy the notes to each line in the range quickly for testing, I had to click the button on the userform sometimes twice before the notes would load to the sheet. Whereas skillilea's approach was much more responsive, only requiring one click per entry. Could also just be my 10-year-old computer.

 

[NoSparks]

I had to click the button on the userform sometimes twice before the notes would load to the sheet.

That makes no sense to me, if the button is clicked, the macro runs, and only dealing with 3 dozen cells I wouldn't think there would be any noticeable time difference.
Anyway it's your project so it doesn't matter to me what route you take, just hoping you get something that works.
My take on erasing something already written to the sheet would go something like this

Private Sub eraseNOTES_Click()
Dim rng As Range, cel As Range, noteCell As Range

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 Left(cel.Value, 4) = scanBOX.Value Then
            cel.MergeArea.ClearContents
            Exit For
        End If
    Next cel
End With

End Sub

If nothing else I think the MergeArea part should help.

 

[Lil Stinker]

If Left(cel.Value, 4) = scanBOX.Value Then

Would this work if the serial number was occasionally 5 digits long?

 

[Lil Stinker]

Would this work if the serial number was occasionally 5 digits long?

Sorry, I should be more specific. The serial numbers are occasionally 5 digits long but also alphanumeric. When I test this with an alphanumeric serial number, it doesn't seem to work even if I adjust the number in Left(cel.Value, 5).

 

[Lil Stinker]

That makes no sense to me, if the button is clicked, the macro runs, and only dealing with 3 dozen cells I wouldn't think there would be any noticeable time difference.

Wouldn't you know it, I did not have the same double-clicking issue in today's testing. It's just my computer.

 

[NoSparks]

Would this work if the serial number was occasionally 5 digits long?

No, but you could use this

        If InStr(1, cel.Value, scanBOX.Value) > 0 Then

 

[Lil Stinker]

Both solutions worked, thank you both for your input. Very much appreciated!

If InStr(1, cel.Value, scanBOX.Value) > 0 Then

This works! I have used InStr before. Never occurred to me to use it here. Still learning.

Thanks again!