Barrie can you figure this one out?

Posted by Kevin on December 05, 2001 6:52 AM

Barrie:

I have the following code inserted into a worksheet, based on ideas for a code I got from you:

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) _
.Activate
If ActiveCell.Address = Target.Address Then
MsgBox ("No Matching ESN Found")
Else
ActiveCell.Offset(0, 7).Select
If Range("B1").Value = 0 Then
Selection.Value = Selection.Value + 1
Else
Selection.Value = Selection.Value + Range("B1").Value
End If
End If
Range("A1").Select
End If
End Sub

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??? Someone else in a previous post said that they thought it was because of cells being formatted as text, but I need to have the cells that contain a UPC code to stay formatted as text, otherwise the number does not always display correctly (for example, some UPC codes begin with a zero digit). The cells in column J where the problem has occurred is not formatted as text.

Thanks in advance for your help,
Kevin

Posted by Kevin on December 05, 2001 9:53 AM

another twist to this problem

I have discovered that sometimes this macro works properly, and then other times it doesn't. for example, if I scan in UPC for an item that already has 8 in the quantity on hand column, it will add a one on the end, making 81, instead of adding 1 + 8 to yield 9. But then the next time I scan the UPC in, it will add 1 to the 81, making 82. This is really strange - any ideas as to why it sometimes works and sometimes doesn't?

Thanks,
Kevin

Posted by Bariloche on December 05, 2001 7:13 PM

Re: another twist to this problem

Kevin,

Don't know if you've noticed or not, but in Excel's quest to make life with spreadsheets easier for people it can sometimes be too helpful.

Have you noticed, for example, how if you sum a range (row or column) of numbers that are formatted as currency, Excel convieniently formats the sum as currency? I'm going to guess that a similar thing has happened here.

Somehow Excel has decided that some of your quantity values should be formated as text. You can see if you can reformat your quantity values as numbers. If that doesn't work (sometimes when Excel gets it in its head that a certain format is correct it refuses to let go), run this macro against your quantity cells and see if it helps any:

'(Note: Select your data range first)

Sub ChangeToNumbers()

For Each cell In Selection

cell.Value = cell.Value * 1
cell.NumberFormat = "0"

Next cell

End Sub

good luck