MrExcel.com - Your One Stop for Excel Tips & Solutions

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) _
.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???

Thanks in advance for your help,
Kevin

Posted by Dan on December 03, 2001 9:04 PM

Hi Kevin. Sorry you are having trouble with this. The only thing I can think of is that some of the cells might be formatted as text. Check that first and let me know. I couldn't duplicate the problem exactly on my machine. Maybe you can send me a copy and I can see if it does the same on my computer? Let me know.

By the way, after the
"ActiveCell.Offset(0, 7).Select" line,
the ".Value" isn't needed in the rest of that macro. For instance, you can have just
"Selection = Selection + 1" etc. Not sure why I did it that way to begin with. You could try deleting those and see what happens. Shot in the dark there, though.

Posted by Dan on December 04, 2001 6:59 AM

I was able to duplicate the problem this morning and it appears that the cell formatting is the probable culprit. You can e-mail me if you have any further problems.

Dan

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

Dan - I need more help

Dan,

Thank you for looking into this further. I am glad that you were able to duplicate my problem. When you said it may be a formatting issue (numbers formatted as text) were you talking about the cells that are searched for, or the cells where the numbers are added on? Because in my search cells, I need to keep them formatted as text. They are UPC codes, and if I don't format them as text some of them do not display properly (for example, UPC's that begin with a zero). Is this a problem?

Thanks,
Kevin

Posted by Dan on December 05, 2001 7:28 PM

Re: Dan - I need more help

I meant the value in B1 or the value in the quantity in the J column (the more likely culprit) are formatted as text. You may want to insert code within the macro that checks the formatting of the cells and changes as necessary before it adds the two values together. Just email me if you still are stuck.

Incidentally, I just worked on someone else's workbook today that had sort of the same problem. It wouldn't sum a column correctly because some values were formatted as text while others were numbers. Even after reformatting the cells to numbers, I had to go in to each cell and edit the cell in order for the change to take effect. Ahhh, Excel.

Posted by Kevin on December 06, 2001 9:22 AM

Re: Dan - I need more help

Dan,
I think that may have been the problem. Cell B1 was formatted as text, so I changed it. This spreadsheet is one that is imported into Excel from QuickBooks, so the cells are all formatted as text initially. So we will have to make sure to reformat after the import but before working in the spreadsheet. Here is something that I found strange: After messing with this, we noticed that sometimes the macro would work correctly and then sometimes it wouldn't. For example, it would take the value of 8 in a cell, and put a 1 on the end instead of adding one, giving us 81. But then we would run the macro again, and it would add 1 to the value 81, giving us 82. If this does turn out to be a formatting issue, I am still perplexed as to why it would sometimes work and sometimes not work. Oh well, I will keep you posted regarding the solution to this problem, and if you come up with any other ideas, I would appreciate you input.

Thank you,
Kevin