Want data entered in userform to be entered in specific areas of worksheet

[jacqu]

Hi,

I'm working on a userform macro where the data entered into it is added to a worksheet. I have the userform all set up and had it working so that it would add the data to the first empty row it found in the worksheet. The code was written as:

Private Sub AddClaimButton_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A"))

'Transfer information
Cells(emptyRow, 1).Value = CustomerNameBox.Value
Cells(emptyRow, 2).Value = CompanyList.Value
Cells(emptyRow, 3).Value = DOLBox.Value
Cells(emptyRow, 4).Value = DateReportedBox.Value
Cells(emptyRow, 5).Value = LossTypeBox.Value
Cells(emptyRow, 6).Value = ClaimStatusList.Value
Cells(emptyRow, 7).Value = AdjusterBox.Value
Cells(emptyRow, 8).Value = DatePaidBox.Value
Cells(emptyRow, 9).Value = AmountPaidBox.Value

If CloseStatusButton1.Value = True Then
    Cells(emptyRow, 10).Value = CloseStatusButton1.Caption
Else
    Cells(emptyRow, 10).Value = CloseStatusButton2.Caption
End If

Unload Me

End Sub

The way I have the worksheet set up there are 5 areas where data should go. I thought to have Option buttons set up to tell the macro where to add the data, added them to the userform and changed the code to:

Private Sub AddClaimButton_Click()

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
If TypeButton1.Value = True Then
    emptyRow = WorksheetFunction.CountA(Range("A8:A13"))

ElseIf TypeButton2.Value = True Then
    emptyRow = WorkhsheetFunction.CountA(Range("A17:A19"))

ElseIf TypeButton3.Value = True Then
    emptyRow = WorksheetFunction.CountA(Range("A23:A35"))

ElseIf TypeButton4.Value = True Then
    emptyRow = WorksheetFunction.CountA(Range("A38:A52"))

Else
    emptyRow = WorksheetFunction.CountA(Range("A56:A1000"))

End If

'Transfer information
Cells(emptyRow, 1).Value = CustomerNameBox.Value
Cells(emptyRow, 2).Value = CompanyList.Value
Cells(emptyRow, 3).Value = DOLBox.Value
Cells(emptyRow, 4).Value = DateReportedBox.Value
Cells(emptyRow, 5).Value = LossTypeBox.Value
Cells(emptyRow, 6).Value = ClaimStatusList.Value
Cells(emptyRow, 7).Value = AdjusterBox.Value
Cells(emptyRow, 8).Value = DatePaidBox.Value
Cells(emptyRow, 9).Value = AmountPaidBox.Value

If CloseStatusButton1.Value = True Then
    Cells(emptyRow, 10).Value = CloseStatusButton1.Caption
Else
    Cells(emptyRow, 10).Value = CloseStatusButton2.Caption
End If

Unload Me

End Sub

The macro still works if I have the first Option Button selected, but if I use any other option it errors and the debugger leads to the "Transfer Information" lines of code. How do I fix this? Or is there an easier way to do this? Also the areas are set up to only have a couple of lines for now so there isn't a bunch of blank space but more lines will be added later, how do I get the macro to account for this? Does it make a difference what I put in this macro if I add another macro later to insert lines by having the user tell it how many lines they need to add? Thanks in advance for any suggestions you have!

 

[jacqu]

Just realized that the first code determining the emptyRow I started with isn't working anymore.

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A"))

I have 4 hidden lines towards the top of my worksheet and the cells in column A are blank so it's finding those and putting the information there. Not sure what made this stop working because it didn't do this before. Probably something to do with playing around trying to get the TypeButtons to work. I've read that using "CountA" is not the best way to find the empty row, but the only alternatives I find are ones that I think find the first empty row searching from the bottom of the sheet up. This would just create a similar problem to what I have now by putting all the information in the last category instead of the first.

 

[jacqu]

Anyone have any ideas for this?