button to find and update info

[static69]

i have created a worksheet to organize my baseball card collection. I created a user form to assist in the input of information. It has 3 text boxes, on for card number, one for name, and one for quantity of the card. When i click the button (called update) it finds the first empty row, and inserts the information where it is supposed to go. As I go through my boxes, i have found several cards that were not where they were already on the list, and when I type them in the form, it just adds them to the bottom of the list. How can i make the button so it looks for the info, if it finds it, it changes whichever information I had to change. (for example, if card 12, Johnny Jones, was already in the spreadsheet and I happened across another one, I just want to type the card number and quantity, not having to type his name over. then just add the one card I found to the 4 already in the spread sheet.) If the card is not already in the spreadsheet, then it would just add it to the end.

here is the code I have used:

Private Sub cmdADD_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("1999 Upper Deck")

'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.txtNumber.Value) = "" Then
Me.txtNumber.SetFocus
MsgBox "Please enter the card number"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtNumber.Value
ws.Cells(iRow, 2).Value = Me.txtQuantity.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value

'clear the data
Me.txtNumber.Value = ""
Me.txtQuantity.Value = ""
Me.txtName.Value = ""
Me.txtNumber.SetFocus

End Sub

please help

[Charles]

Hi,

Not fully tested but try:

Private Sub cmdADD_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim Msg, Style, Title, Help, Ctxt, Response
Title = "Player Exist"
Style = vbYesNo + vbCritical + vbDefaultButton2
Set ws = Worksheets("1999 Upper Deck")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
With UserForm1
If Trim(.txtNumber.Value) = "" Then
Me.txtNumber.SetFocus
MsgBox "Please enter the card number"
Exit Sub
End If
'''' check to see if exist '''
'On Error Resume Next
Cells.Find(What:=.txtname, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

If ActiveCell.Value = .txtname Then
Msg = "Alreadey exist " & .txtname & " Do you want to replace info?"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
ActiveCell.Offset(, -2).Value = .txtNumber
ActiveCell.Offset(, -1).Value = .txtQuantity
.txtNumber.Value = ""
.txtQuantity.Value = ""
.txtname.Value = ""
.txtNumber.SetFocus
Exit Sub
Else
Exit Sub
End If
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = .txtNumber
ws.Cells(iRow, 2).Value = .txtQuantity
ws.Cells(iRow, 3).Value = .txtname
'clear the data
.txtNumber.Value = ""
.txtQuantity.Value = ""
.txtname.Value = ""
.txtNumber.SetFocus
End With


HTH

Charles