having trouble w/ following macro and need help!!!
Posted by Kevin on December 03, 2001 11:16 AM
I have the following code inserted into a worksheet:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
If ActiveCell.Address = Target.Address Then
MsgBox ("No Matching ESN Found")
If Range("B1").Value = 0 Then
Selection.Value = Selection.Value + 1
Selection.Value = Selection.Value + Range("B1").Value
The macro works like this: column A contains upc codes, and column J contains a physical count column.
A user types a upc code into cell A1, and the user also may or may not type a quantity into cell B1. The macro finds the UPC code that was entered into cell A1 and goes to it's location somewhere in column A. Then it goes over to column J in the same row. If a quantity was entered into B1, it adds that quantity to the existing total in that row. Otherwise it adds 1 to the existing total.
However, we are running into some problems with this, because sometimes for some reason, instead of adding the quantity entered in B1 to the total, it is concatenating the two numbers together. For example if there is already a total of 6 in a given cell in column J, and you type 13 in B1, and then type the UPC code for that item in A1, instead of adding 13 to 6 to yield nine, it is sticking the 13 on to the front of the 6, and yielding a total of 136. Does anyone know what is wrong (if anything) w/ the macro, or what could possibly be causing this???
Thanks in advance for your help,