i use a user form to input data into 3 columns. as i inventory i find
duplicate items. when i input the data into the form it just puts the
new info at the bottom of the table. is there a way to make the user
form realize it is about to input duplcate data and just change the 2nd
column (quantity) the other 2 columns are never changing (id) (name).
here is my source code


Private Sub cmdADD_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet

'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 = "1"
Me.txtName.Value = ""
Me.txtNumber.SetFocus

End Sub


--
static69
------------------------------------------------------------------------
static69's Profile: http://www.excelforum.com/member.php...o&userid=11075
View this thread: http://www.excelforum.com/showthread...hreadid=342713

How about something like:

Option Explicit
Private Sub cmdADD_Click()

Dim iRow As Long
Dim res As Variant
Dim ws As Worksheet
Dim rng As Range

Set ws = ActiveSheet

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
Set rng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

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

'res = Application.Match(Me.txtNumber.Value, rng, 0)
res = Application.Match(CLng(Me.txtNumber.Value), rng, 0)

If IsError(res) Then
'new value
'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
Else
'existing value
With rng(res)
.Offset(0, 1).Value = .Offset(0, 1).Value + Me.txtQuantity.Value
'overwrite name??
.Offset(0, 2).Value = Me.txtName.Value
End With
End If

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

End Sub

Note that application match won't find a match between 123 and '123 (text).

And a txtNumber.value will be text--even if you type only digits.

You can convert that text to values with cLng() (whole numbers) or cdbl()
(fractions).

static69 wrote:

i use a user form to input data into 3 columns. as i inventory i find
duplicate items. when i input the data into the form it just puts the
new info at the bottom of the table. is there a way to make the user
form realize it is about to input duplcate data and just change the 2nd
column (quantity) the other 2 columns are never changing (id) (name).
here is my source code

Private Sub cmdADD_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet

'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 = "1"
Me.txtName.Value = ""
Me.txtNumber.SetFocus

End Sub

--
static69
------------------------------------------------------------------------
static69's Profile: http://www.excelforum.com/member.php...o&userid=11075
View this thread: http://www.excelforum.com/showthread...hreadid=342713

--

Dave Peterson