copy cell until data doesnt exitst anymore

[Daniel M]

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.

 

[dbKemp]

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.

 

[Daniel M]

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]

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

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