I have a textbox in a userform, for entering comments. The data should be entered into one of the following cells C44, C47, C50, C53 and C56.
How do I write the data into the first available cell starting at C44? If the cell has data, move to the next cell and so on.
I managed it only for C44.
Private Sub CommandButton1_Click()
Range("c44").Value = Date & ", " & "št. sarže: " & ActiveCell.Value & vbNewLine & TextBox1.Value
Unload UserForm10
End Sub
Here's an example to find the first available cell and write data into it. If all cells are already filled, displays a msgbox.
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = FindFirstAvailableCell
If Not rng Is Nothing Then
rng.Value = Date & ", " & "št. sarže: " & ActiveCell.Value & vbNewLine & TextBox1.Value
Unload UserForm10
Else
MsgBox "All target cells are filled.", vbExclamation
End If
End Sub
Function FindFirstAvailableCell() As Range
Dim targetRange As Range
Dim cell As Range
Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("C44,C47,C50,C53,C56")
For Each cell In targetRange
If cell.Value = "" Then
Set FindFirstAvailableCell = cell
Exit Function
End If
Next cell
Set FindFirstAvailableCell = Nothing
End Function