Macro for splitting lines

[André]

Hi,

I'm close to finally being able to automate a huge and boring job, but I
have a problem which hopefully some of you experts can help me solve.

I have a large excel-sheet with two columns, ItemNo and number (number of
items).

ItemNo. Number
10000 1
10001 2
10002 1
10007 9
etc.

I need a macro that transaltes the above columns to a new sheet within the
same excel-file. The new sheet need to have only one column, which consists
of the two columns joined together. That's easy, but my challange is to give
me 9 lines if the number is 9, AND with the same itemno, but with ascending
numbers from 1 to x. The new file should look like this:

New number
10000-1
10001-1 (number = 2)
10001-2 "
10002-1
10007-1 (number = 9)
10007-2 "
10007-3 "
10007-4 "
10007-5 "
10007-6 "
10007-7 "
10007-8 "
10007-9 "
etc.

Probably an easy task for a programmer, but not for me:-(

Do you have any suggestion on how to solve this?

Best regards,
André

 

[Per Jessen]

Hi André

Try this:

Sub bbb()
Dim TargetSh As Worksheet
Dim Off As Long

Set TargetSh = Worksheets("Sheet2")
FirstRow = 2
LastRow = Range("A1").End(xlDown).Row

For r = FirstRow To LastRow
ItemNo = Cells(r, 1).Value
For ItemCount = 1 To Cells(r, 2).Value
TargetSh.Range("A2").Offset(Off, 0) = ItemNo & "-" & ItemCount
Off = Off + 1
Next
Next
End Sub

Regards,
Per

 

[André]

YES!

That did the trick! I deeply appreciate your help

BR
André

"Per Jessen" <[email protected]> skrev i melding
Hi André

Try this:

Sub bbb()
Dim TargetSh As Worksheet
Dim Off As Long

Set TargetSh = Worksheets("Sheet2")
FirstRow = 2
LastRow = Range("A1").End(xlDown).Row

For r = FirstRow To LastRow
ItemNo = Cells(r, 1).Value
For ItemCount = 1 To Cells(r, 2).Value
TargetSh.Range("A2").Offset(Off, 0) = ItemNo & "-" & ItemCount
Off = Off + 1
Next
Next
End Sub

Regards,
Per