Excel 2010 VBA Userform Coding - Help?

[AmandaSanger]

Hi All,

I've been working on an Excel spreadsheet with a userform. The userform is simply to be used as data entry into the spreadsheet. The spreadsheet contains existing data, we are simply needing to add to the spreadsheet.

At this point, I've got it writing on my next clear row - which is row 246. The problem I've got is that not every row has data present in the "A" column. So I need it to start writing data on row 246 and after.

HOWEVER: Right now, it will write the data to line 246. For the next record entered, it re-writes over the top of line 246. I can't get it to progress forward.

Can anyone please help? This is an urgent "help"... as this is causing a hold-up in a big project.

Here's the existing coding:


Private Sub SaveCommandButton_Click()


ActiveWorkbook.Sheets("Sheet1").Activate

Range("A1").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(245, 0).Select

End If
Loop Until IsEmpty(ActiveCell) = True


With Range("A1")
ActiveCell.Offset(0, 0).Value = Me.CategoryTextBox.Value
ActiveCell.Offset(0, 1).Value = Me.lastnametextbox.Value
ActiveCell.Offset(0, 2).Value = Me.FirstNameTextBox.Value
ActiveCell.Offset(0, 3).Value = Me.TitleTextBox.Value
ActiveCell.Offset(0, 4).Value = Me.BusinessTextBox.Value
ActiveCell.Offset(0, 5).Value = Me.AddressTextBox.Value
ActiveCell.Offset(0, 6).Value = Me.CityTextBox.Value
ActiveCell.Offset(0, 7).Value = Me.StateTextBox.Value
ActiveCell.Offset(0, 8).Value = Me.ZipTextBox.Value
ActiveCell.Offset(0, 9).Value = Me.PhoneTextBox.Value
ActiveCell.Offset(0, 10).Value = Me.EmailTextBox.Value
ActiveCell.Offset(0, 11).Value = Me.CommitteContcatComboBox.Value
ActiveCell.Offset(0, 12).Value = Me.CommentsTextBox.Value


Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End With

End Sub


Your help is truly appreciated!

[jaslake]

Hi AmandaSanger
Welcome to the Forum!!
Please wrap your code in Code Tags...See Rule #3 to see how it's done.
Then find the next available empty row in the worksheet with something like this

Please Login or Register  to view this content.

[AmandaSanger]

Thank you so much! I appreciate your help. This fixed it! Woohoo

[jaslake]

You're welcome.(please wrap your code in Code Tags).