problem with if, then, else

[jlucas]

I am trying to set up a scan mode for a barcode scanner. Right now I have a system set up where I can scan the UPC, and then it searches for that UPC code in the appropriate column. Once found, it offsets to the quantity column and adds one. This seems to work fine, so far at least. But I want to be notified if it does not find the matching UPC so I know what needs to be added to the list. So I have tried to set up an else case with the if, then statement where a message box appears with the notification. However, the message box pops up every time a UPC is entered. Then it runs the else case five times. It seems like the else case is running no matter whether the if statement is true or not.
I am hoping that some fresh (and more experienced) eyes can help my problem. I have attached a copy of the workbook with the code.

[GTO]

Approximately how many barcodes are we starting with?

[jlucas]

Right now, the upc lists that we have are approximately 10,000 unique entries or less per brand. We carry about 20 different brands. So starting off, I want it to work for just the one sheet.

[Bob Phillips]

[vba]

Sub CopyToCell()
Dim TargetCell As Range
If WorksheetFunction.CountIf(Sheets("Sheet1").Columns(1), UserForm1.TextBox1.Value) = 1 Then

Set TargetCell = Nothing
On Error Resume Next
Set TargetCell = Sheets("Sheet1").Columns(1).Find(UserForm1.TextBox1.Value, , xlValues, xlWhole).Offset(0, 1)
On Error GoTo 0
If Not TargetCell Is Nothing Then

TargetCell.Value = TargetCell.Value + 1
Else
MsgBox "UPC not found"
End If
End If

UserForm1.TextBox1.Text = ""
UserForm1.TextBox1.SetFocus
IsActive = False

End Sub[/vba]

[jlucas]

xld-
I'm still not getting the msgbox to show. I copy and pasted the code twice to make sure that I hadn't messed it up somehow. Whenever I enter a random sequence of number that I can see is not part of the UPC list, it just empties out the Text Box and doesn't add a quantity. I tried moving that else case out to the higher if branch, but all that does is display five message boxes every time. Are you have this problem, or is it some user error on my end?
Thanks!

[Bob Phillips]

Does this work for you (it does for me as far as I understand)

[vba]

Sub CopyToCell()
Dim TargetCell As Range
Set TargetCell = Nothing

On Error Resume Next
Set TargetCell = Sheets("Sheet1").Columns(1).Find(UserForm1.TextBox1.Value, , xlValues, xlWhole)
On Error GoTo 0
If Not TargetCell Is Nothing Then

TargetCell.Offset(0, 1).Value = TargetCell.Offset(0, 1).Value + 1
Else

MsgBox "UPC not found"
End If

UserForm1.TextBox1.Text = ""
UserForm1.TextBox1.SetFocus
IsActive = False
End Sub[/vba]

[jlucas]

That works for me as well. However, now when it doesn't find the UPC, it goes to the last unused cell and adds to the quantity column. The value added is based on the number of digits that are typed into the input box. So, if I type 12345, representing a UPC that is not in the list, then it will add a quantity of 4 to the QTY column for the first unused UPC column cell. If you type 1234, then the quantity added becomes 3. Does this happen to you?

I also just noticed that it will add the same relational quantity to this same QTY cell even when it does find a matching UPC in the list: 123123 is a found UPC and it will add 5.

[Bob Phillips]

No it doesn't, it just throws a message. Do you have other code around?

[jlucas]

Besides the altered code that you've helped with, I have a some code in other places. I've got a simple code for the command button that calls the UserForm. Then I have some code with the UserForm:
[vba]
Private Sub CommandButton1_Click()
UserForm1.Hide
Unload UserForm1
End Sub

Private Sub TextBox1_Change()

'Debug.Print "Outside " & IsActive

'To prevent recursion
If Not IsActive And TextBox1.Text <> "" Then
IsActive = True
Application.OnTime Now + TimeValue("00:00:02"), "Module1.CopyToCell"
End If

End Sub

Private Sub UserForm_Initialize()
IsActive = False
TextBox1.SetFocus
End Sub
[/vba] But I don't see anything that would select that cell, nor anything that would add value to it.

Truly, this is something that I could work around, seeing as it doesn't alter the meaningful data in any way. I've attached the file I'm using, for you to see, if that helps.
Also, thank you for all of this help. I have learned much from seeing you work your magic.