user form

[static69]

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

[Dave Peterson]

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