PDA

View Full Version : Find 1st Empty Cell in Range



msquared99
08-08-2012, 10:42 AM
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!

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

CatDaddy
08-08-2012, 11:10 AM
For each cell in Range("D15:D24")
If cell.Value = "" Then
emptycell = cell.Row
Exit For
End If
Next cell

Kenneth Hobs
08-08-2012, 11:16 AM
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

msquared99
08-08-2012, 12:21 PM
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 D15:D24.
Sheets(1).Activate
For Each cell In Range("D15:D24")
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
08-08-2012, 12:37 PM
cell as range

Bob Phillips
08-08-2012, 12:38 PM
Dim cell As Range 'Not Long

msquared99
08-08-2012, 12:53 PM
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 D15:D24.
Sheets(1).Activate

For Each cell In Range("D15:D24")
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
08-08-2012, 12:55 PM
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

msquared99
08-08-2012, 01:15 PM
snb, thanks for the reply, but I only have a range of cells where the data needs to be written to, in cells D15:D24. Other data is in cells D26 and below.

msquared99
08-08-2012, 01:28 PM
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!