create multiple rows in a cell according to quantity entered in specified row

[niwaresm]

I'm using a bar code scanner to enter information in a excel spreadsheet. In cell 2c I have a quantity of 19 so I need to be able to scan 19 tags and 19 serial numbers. My question is how do I get cell 2E (Tag_No) and 2F (Serial) to automatically split into 19 rows or any specified number of rows accoring to the quantity placed in column C?

Example:

Date<o></o>​	<FORM id=Form1> Qty<o></o>​ </FORM>	Verified quantity <o></o>​	Item Description<o></o>​	Tag_No<o></o>​	Serial<o></o>​	Pallet<o></o>​	HIPPO<o></o>​	BFI<o></o>​	Altra B<o></o>​	Furniture Inventory<o></o>​	Technology<o></o>​
9/6/2012<o></o>​	20<o></o>​	<o>19</o>​	tables cafeteria<o></o>	1000907896<o></o>​	144053916505<o></o>​	123456789<o></o>​	2<o></o>​	10<o></o>​	<o></o>	8<o></o>​	<o></o>
<o></o>	8<o></o>​	8<o></o>​	monitors flat<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>
<o></o>	8<o></o>​	8<o></o>​	CPU<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>
<o></o>	15<o></o>​	14<o></o>​	laptop<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>

<TBODY>
</TBODY>

 

[hiker95]

niwaresm,

Welcome to the MrExcel forum.

What version of Excel are you using?

Can we have another screenshot of the results for the row that contains the 19 (manually formatted by you)?

 

[niwaresm]

hello thank you for your reply, im using 2003 Here is another example. When I put in the verified quantity (ie. 19 on the first line) I need tag, serial, pallet etc to allow me to scan 19 differnt barcodes and populate in the cell

Date<o></o>​	Qty<o></o>​	Verified quantity<o></o>​	Item Description<o></o>​	Tag_No<o></o>​	Serial<o></o>​	Pallet<o></o>​	HIPPO<o></o>​	BFI<o></o>​	Altra B<o></o>​	Furniture Inventory<o></o>​	Technology<o></o>​
9/6/2012<o></o>​	20<o></o>​	19<o></o>​	tables cafeteria<o></o>	1000907896 1000907897 1000907898 1000907899 1000907900 1000907901 1000907902 1000907903 1000907904 1000907905 1000907906 1000907907 1000907908 1000907909 1000907910 1000907911 1000907912 1000907913 1000907914<o></o>	144053916505 144053916506 144053916507 144053916518 144053916509 144053916302 144053916400 144053916501 144053916493 144053916300 144053916602 144053916580 144053916577 144053916565 144053916542 144053916530 144053916523 144053916519 144053916595<o></o>	123456789<o></o>​	2<o></o>​	9<o></o>​	<o></o>	8<o></o>​	<o></o>
<o></o>	8<o></o>​	8<o></o>​	monitors flat<o></o>	123456789 123654987 321654895 654123789 958623478 956412389 956247589 325841256 <o></o>	123456789 123654987 321654895 654123789 958623478 956412389 956247589 325841257<o></o>	123456987<o></o>​	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>
<o></o>	8<o></o>​	8<o></o>​	CPU<o></o>	745896541 956859652 452136589 125364589 745896523 652314589 325645872 321524589<o></o>	745896541 956859652 452136589 125364589 745896523 652314589 325645872 321524590<o></o>	564789561<o></o>​	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>
<o></o>	15<o></o>​	14<o></o>​	laptop<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>	<o></o>

<TBODY>
</TBODY>

 

[hiker95]

niwaresm,

I can understand the numbering sequence for the Tag_No's for the 19 numbers.

But, I can not understand where the sequencing is coming from for the Serial numbers.


Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[niwaresm]

Bump

 

[Momentman]

A worksheet event would be appropriate but I decided to use a macro instead which you would run after entering the value of theVerified tag in column C. It would create the spaces you need in columns E,F and G

Sub Macro2()
    n = ActiveCell.Row
    k = Cells(n, "C").Value
    msg = Cells(n, "E").Value
    msg2 = Cells(n, "F").Value
    msg3 = Cells(n, "G").Value
    
    For I = 1 To k
        msg = msg & Chr(10)
        msg2 = msg2 & Chr(10)
        msg3 = msg3 & Chr(10)
    Next I
    
    Cells(n, "E").Value = msg
    Cells(n, "F").Value = msg2
    Cells(n, "G").Value = msg3
End Sub

 

[niwaresm]

Thank you so much. Do I have to run this macro for each line? Is there a way for me to run it for the entire spreadsheet so as I add lines/ quantities in cell C the macro will automatically run in E F and G?

 

[Momentman]

Yes there is.

But have you run it to at least confirm that it does what you require??

 

[niwaresm]

Yes I ran it and it works one row at a time thanks

 

[Momentman]

You could use the worksheet event like this:

Instead of placing the code in a module, place in the particular worksheet that the code is required to work on

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C1000")) Is Nothing Then
        n = ActiveCell.Row
        k = Cells(n, "C").Value
        msg = Cells(n, "E").Value
        msg2 = Cells(n, "F").Value
        msg3 = Cells(n, "G").Value
        
        For I = 1 To k
            msg = msg & Chr(10)
            msg2 = msg2 & Chr(10)
            msg3 = msg3 & Chr(10)
        Next I
        
        Cells(n, "E").Value = msg
        Cells(n, "F").Value = msg2
        Cells(n, "G").Value = msg3
                
    End If
    
End Sub