Macro is cycling

**robfet** · 06-30-2011, 11:53 AM

Hello everyone.

I'm having problems with a code on a macro.

I get a bar code with a bar code scanner and automatically fills a cell on excel. So if a bar code has a "n" number it will fill the cell with color and make a beep. Everything is good but the beep it seems to be cycling and it beeps whatever the cells value is after the first beep.

Please Login or Register  to view this content.

hope some one can help me.

thanks

**MarvinP** · 06-30-2011, 12:13 PM

Hi robfet and welcome to the forum,

You should first click on the Green EDIT button below your post and then click on the "Go Advanced" below the message area. Then select the text that is code and click on the "#" Icon above the message area to put code tags around your code. This will then follow the rules of the forum and make your code easier for us to deal with.

It looks like your code will see if there is a cell value of 32884173068 anywhere in Column A (down to row 1000) whenever anything on the worksheet changes. I don't think that is what you want.

You need to learn this line of code to be more efficenent:

Please Login or Register  to view this content.

see http://www.ozgrid.com/VBA/vba-intersect.htm

**robfet** · 07-07-2011, 12:17 PM

Thanks Marvin.

I saw many examples of intersect method. So what you advise me is to use that method, but i'm getting confused about how to place the code where if a number is typed on a cell fill it with a color. :s then beep.... I tried many ways but it doesn't run because of errors....

**snb** · 07-07-2011, 12:27 PM

I think this suffices:

Please Login or Register  to view this content.

**robfet** · 07-07-2011, 12:48 PM

snb,

when the first 32884173068 number is typed it works fine, it beeps and the cell get the color but when another number is typed, different to 32884173068 the cells turns to color red too, it doesn't beep but the color changed.....

**snb** · 07-07-2011, 03:25 PM

Please, post your example workbook in which this happens here.

**robfet** · 07-07-2011, 08:12 PM

I changed the code... now its working

Please Login or Register  to view this content.

Why I need this to work? We need to scan bar-codes from boxes, so we need to make sure if is the same bar code and if is not make a warning with this sound and color cell.

Is there a way to clear and stay in the same cell? We need to scan over 15,000 boxes so every time the scanner got a bar code pass it to the next cell. So I'm wondering if its possible to clear the cell value after the bar code has been scanned and then stay in that same cell waiting to another bar code?