macro help please
 

I probably posted this before in the wrong place. I need help with a
macro. I am reading data (serial number) into excel from a scanner
(using {RSLINX/sort_scanners!"st9:0,l2,c2'} and this puts the serial
number in one cell. Without a macro, it just keeps overwriting the
next data read to the same cell, and I need the next serial number to
appear in the cell below the one before it. Any help greatly
appreciated!


--
Bornloser
------------------------------------------------------------------------
Bornloser's Profile: http://www.excelforum.com/member.php...o&userid=30411
View this thread: http://www.excelforum.com/showthread...hreadid=501157

macro help please
 

Could you not use a loop. The for each loop would loop through all
cells in the range Range. On each iteration of the loop the data is
added to the next cell.

For Each MyCell In Range

Enter data in MyCell

Next MyCell



Or maybe the offset function might help.


Kind regards,

Tom


--
Tomski
------------------------------------------------------------------------
Tomski's Profile: http://www.excelforum.com/member.php...o&userid=26824
View this thread: http://www.excelforum.com/showthread...hreadid=501157

macro help please
 

Hi Bornloser
Try this
Put this in codemodul for the sheet you use,
her I use the column at left of input column.
I.e Column "A" = serienum, column "B" your inputt.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target < "" Then
Target.Offset(0, -1) = Target.Offset(-1, -1) + 1
End If
Application.EnableEvents = True
End Sub

Regards Yngve

macro help please
 

Might also want to test to see if the new cell location is not "off the
chart" prior to moving to the next column down. If so, you could force it to
start somewhere new, depending on your next "available" location. Just a
forethought.


"Yngve" wrote:

Hi Bornloser
Try this
Put this in codemodul for the sheet you use,
her I use the column at left of input column.
I.e Column "A" = serienum, column "B" your inputt.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Target < "" Then
Target.Offset(0, -1) = Target.Offset(-1, -1) + 1
End If
Application.EnableEvents = True
End Sub

Regards Yngve

macro help please
 

There will be approximately 2000 per column per day....is this going to
put it "off the chart" and if so, what is the best way to put it on a
new page. I know nothing of Excel, I do electronics.
Thanks


--
Bornloser
------------------------------------------------------------------------
Bornloser's Profile: http://www.excelforum.com/member.php...o&userid=30411
View this thread: http://www.excelforum.com/showthread...hreadid=501157

macro help please
 

Downwards, 2000 in a column is not a problem. However as you move from left
to right, you will hit a limit of 256. Considering that you are trying to do
this for every day of the year, (or at least business day). You will need to
come up with some method of moving to the next worksheet as the number of
days increases. You might break it up into 4 worksheets corresponding with
quarters, or 2 worksheets with the first "half" of the year on one, and the
2nd on the next, or just two worksheets so that whenever you get to the end
of the first worksheet, you go to the next. That all depends on what you
plan to do.

My concern was that if you continued to add to the end of a column, like
2000 today and 2000 tomorrow thus extending the column to be 4000 long, that
you would eventually hit a maximum. I think the maximum number of rows is in
the vicinity of 65,536. So 2000 rows in a column will not be a problem.

As for moving to a new page... Well, if you are electronics trained, you
most likely have had to do a great deal of mathematical work. I would think
that you could figure out a good equation to get what you want. It's just a
matter of turning your "wants" into actions understood by Excel. If that's
the problem, just let us know somewhat specifically what you want to occur,
and I am sure that someone would be able to help you out.

"Bornloser" wrote:


There will be approximately 2000 per column per day....is this going to
put it "off the chart" and if so, what is the best way to put it on a
new page. I know nothing of Excel, I do electronics.
Thanks


--
Bornloser
------------------------------------------------------------------------
Bornloser's Profile: http://www.excelforum.com/member.php...o&userid=30411
View this thread: http://www.excelforum.com/showthread...hreadid=501157