Userform, find next empty row but ignore a formula in column, please help!

[Bevo75]

Hi all,

I'm both new to VBA and new to this forum. I am using Windows 7 and Excel 2007.

The userform I created adds data to a register and was working fine. The form updates consecutive columns, however the last column holds a function that I dragged down through several thousands of records.

I need the userform to ignore this column and still enter the data in the consecutive columns, at the moment it is entering data into the next empty row following the last row where my function has been dragged to.

I would be grateful for your help, I just can't seem to find a way to sort it....

The VBA code for the form is:

Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.cboType.Value = "" Then
MsgBox "Please enter Type.", vbExclamation, "Data Input Form"
Me.cboType.SetFocus
Exit Sub
End If
If Me.DTPicker1.Value = "" Then
MsgBox "Please enter Date Raised.", vbExclamation, "Data Input Form"
Me.cboType.SetFocus
Exit Sub
End If
If Me.txtRaisedBy.Value = "" Then
MsgBox "Please select Raised By.", vbExclamation, "Data Input Form"
Me.cboType.SetFocus
Exit Sub
End If
If Me.txtCustomer.Value = "" Then
MsgBox "Please enter Customer Name.", vbExclamation, "Data Input Form"
Me.cboType.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtWoNo.Value) Then
MsgBox "Works Order must contain a number only.", vbExclamation, "Data Input Form"
Me.txtWoNo.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtSalesOrderNo.Value) Then
MsgBox "Sales Order must contain a number only.", vbExclamation, "Data Input Form"
Me.txtSalesOrderNo.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtQtyInError.Value) Then
MsgBox "Qty in error must contain a number only.", vbExclamation, "Data Input Form"
Me.txtQtyInError.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtQtyToBeReworked.Value) Then
MsgBox "Qty to be reworked must contain a number only.", vbExclamation, "Data Input Form"
Me.txtQtyToBeReworked.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtQtyToBeScrapped.Value) Then
MsgBox "Qty to be scrapped must contain a number only.", vbExclamation, "Data Input Form"
Me.txtQtyToBeScrapped.SetFocus

Exit Sub
End If
' Write data to worksheet
RowCount = Worksheets("Register").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Register").Range("B1")
.Offset(RowCount, 0).Value = Me.txtReportNo.Value
.Offset(RowCount, 1).Value = Me.cboType.Value
.Offset(RowCount, 2).Value = Me.DTPicker1.Value
.Offset(RowCount, 3).Value = Me.txtRaisedBy.Value
.Offset(RowCount, 4).Value = Me.txtCustomer.Value
.Offset(RowCount, 5).Value = Me.cboSupplier.Value
.Offset(RowCount, 6).Value = Me.txtWoNo.Value
.Offset(RowCount, 7).Value = Me.txtOpNo.Value
.Offset(RowCount, 8).Value = Me.txtDrawingNo.Value
.Offset(RowCount, 9).Value = Me.txtIssue.Value
.Offset(RowCount, 10).Value = Me.txtSalesOrderNo.Value
.Offset(RowCount, 11).Value = Me.cboPrefix.Value
.Offset(RowCount, 12).Value = Me.txtPurchaseOrderNo.Value
.Offset(RowCount, 13).Value = Me.txtGRNNo.Value
.Offset(RowCount, 14).Value = Me.DTPicker2.Value
.Offset(RowCount, 16).Value = Me.txtDetails.Value
.Offset(RowCount, 17).Value = Me.txtNCRDetails.Value
.Offset(RowCount, 18).Value = Me.txtImmediateAction.Value
.Offset(RowCount, 19).Value = Me.cboManagerResponsible.Value
.Offset(RowCount, 20).Value = Me.txtQtyInError.Value
.Offset(RowCount, 21).Value = txtQtyToBeReworked.Value
.Offset(RowCount, 22).Value = txtQtyToBeScrapped.Value
.Offset(RowCount, 23).Value = txtQtyReceived.Value
.Offset(RowCount, 24).Value = txtQtyRejected.Value
.Offset(RowCount, 25).Value = txtQtyAcceptedUnderCon.Value
Me.txtReportNo = Application.WorksheetFunction.Max(Sheets("Register").Range("B:B")) + 1
Me.DTPicker1 = Date


End With

Unload Me
frmDEF.Show
End Sub

Private Sub CommandButton1_Click()
frmActions.Show
End Sub

Private Sub CommandButton3_Click()
Unload Me
End Sub

Private Sub txtCustomer_Change()
txtCustomer = StrConv(txtCustomer, vbProperCase)
End Sub

Private Sub txtDrawingNo_Change()
txtDrawingNo = UCase(txtDrawingNo)
End Sub


Private Sub txtRaisedBy_Change()
txtRaisedBy = StrConv(txtRaisedBy, vbProperCase)
End Sub

Private Sub UserForm_Initialize()
'Next report number
Me.txtReportNo = Application.WorksheetFunction.Max(Sheets("Register").Range("B:B")) + 1
Me.DTPicker1.Value = Date
Me.DTPicker2.Value = Date
Me.DTPicker2.Enabled = False

End Sub

Many thanks for your time, look forward to hearing from you.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Try changing this line:

RowCount = Worksheets("Register").Range("B1").CurrentRegion.Rows.Count

into this one:

RowCount = Worksheets("Register").Cells(Rows.Count, "B").End(xlUp).Row

That will count only the rows in column B.

 

[Bevo75]

Spot on RickXL.....worked a treat.....really appreciate your help!! Thank you!!

 

[RickXL]

You're welcome and thanks for the feedback.