UserForm Question Need Help!

[jfcby]

Hello,

PLEASE HELP! Excel 2003

After I created my userform I found some code but I can't get it to
work the way I need it to!

My user form is for one worksheet it has 3 text boxes, a OK button, and
a close button. I need the code to insert data into cells a6, b6, c6 if
there is no data already there. If data is in the cells I need it to
find the first empty cell within the range and insert data from the
userform textboxes.

The Code:

Private Sub CommandButton1_Click()

Dim rng As Range
Dim rng2 As Range
Set rng2 = Range("A6:A20")
On Error Resume Next
Set rng = rng2.Columns(1).SpecialCells(xlConstants)
On Error GoTo 0
If Not rng Is Nothing Then
'Insert data in blank cell
Range(A6).Value = TextBox1.Value
Range(B6).Value = TextBox2.Value
Range(C6).Value = TextBox3.Value
Else

'Find empty cell within range
Set r = Worksheets("TEST - user forms").Range("A6:A20")
' change name of worksheet and range to your needs
found = False
For Each C In r
If IsEmpty(C) Then
r.Worksheet.Activate
C.Select
found = True
Exit For
End If
Next
If Not found Then MsgBox "No empty cell found within the range"

'Insert data in blank cell
ActiveCell.Offset(1, 0).Value = TextBox1.Value
ActiveCell.Offset(1, 1).Value = TextBox2.Value
ActiveCell.Offset(1, 2).Value = TextBox3.Value

End If

'Closes Userform
Unload UserForm1

End Sub

Thank you for your help,
jfcby

 

[Jim Rech]

This is how I'd do it:

Private Sub CommandButton1_Click()
Dim CellsUsed As Integer
With Range("A6:A20")
CellsUsed = Application.CountA(.Cells)
If CellsUsed = .Cells.Count Then
MsgBox "Range full"
Else
With .Cells(1).Offset(CellsUsed)
.Value = TextBox1.Value
.Offset(0, 1).Value = TextBox2.Value
.Offset(0, 2).Value = TextBox3.Value
End With
End If
End With
End Sub

 

[jfcby]

Hello Jim,

Thank you for yor help the code works great!

jfcby