I have a macro i need to write that does the following. select a cell (A1)
and start scanning in serial number with my barcode scanner.

The data looks like this "02-20025002-00583802-00747102-006914"

I then put this formula into A2. "=MID($A$1,1+(ROW()-2)*9,9)"

This splits the long string into individual serial numbers. I then have to
copy that formula into A3-Ax until the data stops filling in. Then i copy the
data and paste special values to get just the data i want and delete A1.

The questions is can i make this formula paste until it stops putting in
data and then do the rest of the steps without having to manually do it? Or
is there some other way to do it? Thanks.

dm.

On Jan 18, 9:38 am, Daniel M <Dani...@discussions.microsoft.com>
wrote:
> I have a macro i need to write that does the following. select a cell (A1)
> and start scanning in serial number with my barcode scanner.
>
> The data looks like this "02-20025002-00583802-00747102-006914"
>
> I then put this formula into A2. "=MID($A$1,1+(ROW()-2)*9,9)"
>
> This splits the long string into individual serial numbers. I then have to
> copy that formula into A3-Ax until the data stops filling in. Then i copy the
> data and paste special values to get just the data i want and delete A1.
>
> The questions is can i make this formula paste until it stops putting in
> data and then do the rest of the steps without having to manually do it? Or
> is there some other way to do it? Thanks.
>
> dm.

It looks like the serial numbers are separated by '-'. If so, you
could use the split function with '-' as a delimeter and then write
the array to the appropriate Excel range.

No the serial numbers are not split with a -. they are 02-123456 format. so
running all together you see 02-12345602-12345702-123458


"dbKemp" wrote:

> On Jan 18, 9:38 am, Daniel M <Dani...@discussions.microsoft.com>
> wrote:
> > I have a macro i need to write that does the following. select a cell (A1)
> > and start scanning in serial number with my barcode scanner.
> >
> > The data looks like this "02-20025002-00583802-00747102-006914"
> >
> > I then put this formula into A2. "=MID($A$1,1+(ROW()-2)*9,9)"
> >
> > This splits the long string into individual serial numbers. I then have to
> > copy that formula into A3-Ax until the data stops filling in. Then i copy the
> > data and paste special values to get just the data i want and delete A1.
> >
> > The questions is can i make this formula paste until it stops putting in
> > data and then do the rest of the steps without having to manually do it? Or
> > is there some other way to do it? Thanks.
> >
> > dm.
>
> It looks like the serial numbers are separated by '-'. If so, you
> could use the split function with '-' as a delimeter and then write
> the array to the appropriate Excel range.
>

On Jan 18, 5:41 pm, Daniel M <Dani...@discussions.microsoft.com>
wrote:
> No the serial numbers are not split with a -. they are 02-123456 format. so
> running all together you see 02-12345602-12345702-123458
>
> "dbKemp" wrote:
> > On Jan 18, 9:38 am, Daniel M <Dani...@discussions.microsoft.com>
> > wrote:
> > > I have a macro i need to write that does the following. select a cell (A1)
> > > and start scanning in serial number with my barcode scanner.
>
> > > The data looks like this "02-20025002-00583802-00747102-006914"
>
> > > I then put this formula into A2. "=MID($A$1,1+(ROW()-2)*9,9)"
>
> > > This splits the long string into individual serial numbers. I then have to
> > > copy that formula into A3-Ax until the data stops filling in. Then i copy the
> > > data and paste special values to get just the data i want and delete A1.
>
> > > The questions is can i make this formula paste until it stops putting in
> > > data and then do the rest of the steps without having to manually do it? Or
> > > is there some other way to do it? Thanks.
>
> > > dm.
>
> > It looks like the serial numbers are separated by '-'. If so, you
> > could use the split function with '-' as a delimeter and then write
> > the array to the appropriate Excel range.

Try this:
Public Sub Main()
SplitSN Target:=ActiveCell
End Sub

Private Sub SplitSN(ByRef Target As Excel.Range)
Dim sInput As String
Dim sOutput() As String
Dim lUpperBound As Long
Dim lCtr As Long

sInput = Target.Value
lUpperBound = (Len(sInput) / 9) - 1
ReDim sOutput(lUpperBound)
For lCtr = 0 To lUpperBound
sOutput(lCtr) = Mid$(sInput, lCtr * 9 + 1, 9)
Next
Target.Offset(1, 0).Resize(lUpperBound + 1, 1).Value =
Application.WorksheetFunction.Transpose(sOutput)
End Sub