combobox selection adding new row to worksheet

[buckchow]

I've been working on this problem and am stumped. When I click on an item in the userform combobox all the data is transferred to the next empty row instead of the existing item row on the worksheet and I end up with duplicates. What I would like to accomplish is if the item already exists in the combobox then add the userform data to the existing row, otherwise if the item does not exist I would like the item and data to be transferred to the next empty row.

[T-J]

You need to search for the item on the sheet and if it exists add data to the row where found, otherwise transfer data to the next empty row.
The Find method is useful for searching a sheet.

Below is a function FindIt which searches for a string on a worksheet and returns the row number where found. Then it's just a matter of adding your code for adding to itemRow or transferring to the next empty row.

Please Login or Register  to view this content.

[buckchow]

Thanks for the response T-J. I've tried entering your code but am now receiving an error so I've attached my original code. The name of my combobox is txtBatch1 and my named range for the combobox is "Batch" which is located on sheet 2.


Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
txtBatch1.SetFocus
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtBatch1.Value) = "" Then
Me.txtBatch1.SetFocus
MsgBox "Please enter a batch number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBatch1.Value
ws.Cells(iRow, 2).Value = Me.txtDate1.Value
ws.Cells(iRow, 3).Value = Me.txtCust1.Value
ws.Cells(iRow, 4).Value = Me.txtBoard1.Value
ws.Cells(iRow, 5).Value = Me.txtSerial1.Value
ws.Cells(iRow, 6).Value = Me.txtQty1.Value
ws.Cells(iRow, 18).Value = Me.txtStatus1.Value
ws.Cells(iRow, 17).Value = Me.txtNotes.Value

'clear the data
Me.txtBatch1.Value = ""
Me.txtDate1.Value = ""
Me.txtCust1.Value = ""
Me.txtBoard1.Value = ""
Me.txtSerial1.Value = ""
Me.txtQty1.Value = ""
Me.txtStatus1.Value = ""
Me.txtNotes.Value = ""
Me.txtBatch1.SetFocus

End Sub

[T-J]

Probably a misunderstanding on my part. Comment out the Private Sub ComboBox1_Click() above and in your Private Sub CommandButton1_Click() code replace this part:

Please Login or Register  to view this content.

[buckchow]

'FindIt' produces an error

[T-J]

It works ok for me. What is the error and line?

[buckchow]

'FindIt' is highlighted with the following error: Compile error: Sub or Function not defined.

[T-J]

It must be the line itemRow = FindIt(txtBatch1.Value) in CommandButton1_Click()
If you click Help in the error message you get

A Sub, Function, or Property procedure must be defined to be called. This error has the following causes and solutions:
The specified procedure isn't visible to the calling procedure.

Place the FindIt code in the userform code or in a code module and it should work.