Find 1st Empty Cell in Range

[msquared99]

My brain hurts from this. I have a UserForm where the person checks a box if the client has a particular service. On the spreadsheet (Sheet1) in cell D14 is the Service Name. I have up to 7 different services the user can check. If the CheckBox has been checked I want the name of the service to populate in a cell beginning in cell D15. One to all the services can be checked. My range is D15:D24.

I've tried several different lines of code and nothing works. In the code below I get Run Time Error 1004.

Thanks, for any help!

[VBA]Private Sub cmdProcess_Click()
Dim emptyCell As Range

'Activate Sheet1, find the empty cell and if CheckBox is checked write the "Value" to the cell in Range D15:D24.
Sheets(1).Activate
emptyCell = WorksheetFunction.CountA(Range("D14:D")) + 1

If cbTime.Value = True Then Cells(emptyCell, 4).Value = "Time Import"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "Cash Cards"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "ESS"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "RPS Client"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "ATEST Client"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "HRSC"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "Hartford EP"

'Copies data from UserFrom into Sheet1
With Sheet1
.Range("B1").Value = Me.tbCOID.Value
.Range("D1").Value = Me.tbName.Value
.Range("H1").Value = Me.tbDate.Value
.Range("B3").Value = Me.tbEECount.Value
.Range("D3").Value = Me.tbHourlyEE.Value
.Range("F3").Value = Me.tbHourlyPer.Value
.Range("B5").Value = Me.tbRep.Value
.Range("B7").Value = Me.tbContact.Value
.Range("F7").Value = Me.tbContactNum.Value
.Range("B9").Value = Me.tbScore1.Value
.Range("B11").Value = Me.tbScore2.Value
.Range("D9").Value = Me.tbSurveyNotes.Value
.Range("F15").Value = Me.tbSalesForce.Value
.Range("B15").Value = Me.CbxPayroll.Value
.Range("B16").Value = Me.CbxCOBRA.Value
.Range("B17").Value = Me.CbxFSA.Value
.Range("B18").Value = Me.CbxEMS.Value
.Range("B19").Value = Me.CbxTLM.Value
.Range("B20").Value = Me.CbxBenexx.Value

End With

'Clear UserForm

End Sub[/VBA]

[CatDaddy]

[VBA]For each cell in Range("D1524")
If cell.Value = "" Then
emptycell = cell.Row
Exit For
End If
Next cell[/VBA]

[Kenneth Hobs]

[VBA]Sub test()
Dim st As Worksheet, emptyCell As Range
Set st = Worksheets(1)
Set emptyCell = st.Range("D25").End(xlUp).Offset(1)
If emptyCell.Row < 15 Then Set emptyCell = st.Range("D15")
MsgBox emptyCell.Address
End Sub
[/VBA]

[msquared99]

I get a compile error. What am I doing wrong?

Thanks,

Private Sub cmdProcess_Click()
Dim emptyCell As Range
Dim cell As Long

'Activate Sheet1, find the empty cell and if CheckBox is checked write the "Value" to the cell in Range D1524.
Sheets(1).Activate
For Each cell In Range("D1524")
If cell.Value = "" Then
emptyCell = cell.Row
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "Time Import"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "Cash Cards"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "ESS"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "RPS Client"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "ATEST Client"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "HRSC"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "Hartford EP"
Exit For
End If
Next cell
End Sub

[CatDaddy]

cell as range

[Bob Phillips]

[VBA]Dim cell As Range 'Not Long[/VBA]

[msquared99]

I get a run-time error 91. When I hoover over emptyCell it equals nothing, but cell.Row = 15

Private Sub cmdProcess_Click()
Dim emptyCell As Range
Dim cell As Range

'Activate Sheet1, find the empty cell and if CheckBox is checked write the "Value" to the cell in Range D1524.
Sheets(1).Activate

For Each cell In Range("D1524")
If cell.Value = "" Then
emptyCell = cell.Row
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "Time Import"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "Cash Cards"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "ESS"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "RPS Client"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "ATEST Client"
If cbCashCards.Value = True Then Cells(emptyCell, 4).Value = "HRSC"
If cbTime.Value = True Then Cells(emptyCell, 4).Value = "Hartford EP"
Exit For
End If
Next cell

[snb]

[VBA]

Private Sub cmdProcess_Click()
with sheet1
If cbTime.Value Then .cells(rows.count,4).end(xlup).offset(1).Value = "Time Import"
If cbCashCards.Value Then .cells(rows.count,4).end(xlup).offset(1).Value = "Cash Cards"
If cbTime.Value Then .cells(rows.count,4).end(xlup).offset(1).Value = "ESS"
If cbCashCards.Value Then .cells(rows.count,4).end(xlup).offset(1).Value = "RPS Client"
If cbTime.Value Then .cells(rows.count,4).end(xlup).offset(1).Value ="ATEST Client"
If cbCashCards.Value Then .cells(rows.count,4).end(xlup).offset(1).Value = "HRSC"
If cbTime.Value Then .cells(rows.count,4).end(xlup).offset(1).Value = "Hartford EP"

.Range("B1").Value = tbCOID.Value
.Range("D1").Value = tbName.Value
.Range("H1").Value = tbDate.Value
.Range("B3").Value = tbEECount.Value
.Range("D3").Value = tbHourlyEE.Value
.Range("F3").Value = tbHourlyPer.Value
.Range("B5").Value = tbRep.Value
.Range("B7").Value = tbContact.Value
.Range("F7").Value = tbContactNum.Value
.Range("B9").Value = tbScore1.Value
.Range("B11").Value = tbScore2.Value
.Range("D9").Value = tbSurveyNotes.Value
.Range("F15").Value = tbSalesForce.Value
.Range("B15").Value = CbxPayroll.Value
.Range("B16").Value = CbxCOBRA.Value
.Range("B17").Value = CbxFSA.Value
.Range("B18").Value = CbxEMS.Value
.Range("B19").Value = CbxTLM.Value
.Range("B20").Value = CbxBenexx.Value
End With
End Sub[/VBA]

[msquared99]

snb, thanks for the reply, but I only have a range of cells where the data needs to be written to, in cells D1524. Other data is in cells D26 and below.

[msquared99]

I figured it out! Made the following change.

If cbTime.Value Then .cells(25, 4).end(xlup).offset(1).Value = "Time Import"

It works perfect!

Thanks for all the help!