Insert Lines & sequential numbers based on quantity

Closed
Coley925
Posts
10
Registration date
Tuesday March 22, 2016
Status
Member
Last seen
March 31, 2016
- Mar 22, 2016 at 10:29 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Apr 4, 2016 at 12:00 PM
I have an order form with multiple macros including a macro to "Total" each order to show only the items and the quantity of each item that is ordered at one time. I am in desperate need of a macro that can take ONLY the items being ordered, and generate another table below my order form and list serial numbers in a numerical sequence based on the quantity being ordered. This has become difficult for me to build since the visible rows, serial numbers, and quantity change between orders. Also, please keep in mind that the excel form is used as a template, that I copy the information from and paste into another program to actually place the order. With that being said, the number of columns in each table has to stay the same, blank columns need to stay visible, and the serial numbers have to go in the same column each time. Otherwise, I have to manually type out ALL of this information.




After I apply my "Total" macro, I enter the beginning serial# for each item



Beginning on Row 70, my second table looks like this



I need a macro that will take the information from table 1 and list it in table 2 like this



Does this make sense? It is possible?

6 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Mar 22, 2016 at 12:39 PM
Hi Coley925,

Let me see if get this right.

Looking at your second pic, you want to copy the item in column B the number of times listed in column C to column H of pic 4. The serial number from column A (pic 2) will be placed in column G (pic 4) and increments by 1. The rest of the columns (pic 4) will be left alone.

Correct?

Best regards,
Trowa
0
Coley925
Posts
10
Registration date
Tuesday March 22, 2016
Status
Member
Last seen
March 31, 2016

Mar 22, 2016 at 01:05 PM
YES, YES, YES!

I realize it is kind of redundant, but I copy & paste each table in a different screen in our ordering program.

I have tried different macros, but they only work on the fields the macro was built on. Since the items, quantity, and serial numbers change from each order, I haven't been able to find one flexible enough. My final table (pic4) can be 200+ serial numbers sometimes!

If this can be done, it would save me and my co-workers HOURS every day!

I use different set-ups for our different vendors, but once I see the macro that will work correctly, I can make adjustments as needed for each one of my vendors.

I truly appreciate the help!!!
0
Coley925
Posts
10
Registration date
Tuesday March 22, 2016
Status
Member
Last seen
March 31, 2016

Mar 22, 2016 at 03:57 PM
If you don't mind, could you explain the macro to me as well. Where everything comes from, going, etc. If possible..

I found one posted in an earlier thread, tried to make adjustments to it, ran it, and it crashed my file!

That's why I want to understand it before I try to make changes to it.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Mar 24, 2016 at 12:33 PM
Hi Coley,

See if the following code does what it needs to do:
Sub RunMe()
Dim x, qty, fRow, sn As Integer

x = 8

Do
    qty = Cells(x, "C").Value
    If qty > 0 Then
        
        fRow = Range("H" & Rows.Count).End(xlUp).Row
        Cells(x, "B").Copy Range(Cells(fRow + 1, "H"), Cells(fRow + qty, "H"))
        sn = Cells(x, "A")
        For Each cell In Range(Cells(fRow + 1, "G"), Cells(fRow + qty, "G"))
            cell.Value = sn
            sn = sn + 1
        Next cell
    End If
    x = x + 2
Loop Until x = 56

End Sub


Didn't have enough time to explain the code. But at least you can try it out.

Maybe the code makes more sense to you then you thought, so let me know what is unclear to you and I'll be happy to clarify.

Best regards,
Trowa
0
Coley925
Posts
10
Registration date
Tuesday March 22, 2016
Status
Member
Last seen
March 31, 2016

Mar 24, 2016 at 02:04 PM
Unfortunately, it didn't work. I get a runtime error and it shows me the yellow arrow at sn = Cells(x, "A")
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Mar 29, 2016 at 11:23 AM
Hi Coley,

That could mean that the serial number you are actually using is not a number.

Here is the book I tested the code on:
http://ge.tt/2DliwGZ2

Consider uploading your own workbook (careful with sensitive info) for me to look where it is going wrong.

Best regards,
Trowa
0
Coley925
Posts
10
Registration date
Tuesday March 22, 2016
Status
Member
Last seen
March 31, 2016

Mar 29, 2016 at 01:10 PM
Hi TrowaD,

I thought about that when I first tried it and formatted those cells to be numbers, but it still wouldn't work.

I have been manually entering the information, and it has helped my process, but if I can get this macro to do what I want, it would be FANTASTIC!

As I have been manually entering it in, I have had to make a few changes to my set-up. With that being said, I am going to make a generic workbook with all of my buttons, macros, and formulas so that you can take a look at it. I will also include directions of my process so that maybe it might make more sense.

I genuinely appreciate all of your efforts to help me!!!!

Thanks,
Coley
0
Coley925
Posts
10
Registration date
Tuesday March 22, 2016
Status
Member
Last seen
March 31, 2016

Mar 31, 2016 at 08:15 AM
So, I didn't have time to make a generic one so, I just removed the pricing and took some actual screen shots. The general setup stays the same between worksheets. the only thing that varies is where the asset/serial numbers begin. I need the macro to use the quantity in table1 columnC and generate a sequential list starting with the beginning number in table1 ColumnI and placing it in table2 Column G. I also need the corresponding description from table1 Column B to be placed in table2 ColumnI. I will be assigning the macro to the "Generate Asset Numbers" button. When this macro is ran, does a certain cell need to be highlighted, or will it run no matter what cell my cursor is in? Also, can you tell me what needs to be changed within the macro when my table2 begins on a different row between worksheets?

Table 1-


Table 2 - Rows 1-7 stay visible
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Mar 31, 2016 at 11:42 AM
Hi Coley,

Question 1:
"When this macro is ran, does a certain cell need to be highlighted, or will it run no matter what cell my cursor is in?"
Answer: The code will run no matter what cell or range is selected.

Question 2:
"Also, can you tell me what needs to be changed within the macro when my table2 begins on a different row between worksheets?"
Answer: Code line 10 will determine the first row in second table based on column H. The way this is done is by going to the last cell of column H (hit the End button and then Arrow down a couple of times until you get to the last row.) and hit End and Arrow up. The row your cursor is now on will be the as the header row of table 2. So if that works correctly, then no adjustments are needed for different sheets with a different starting row of table 2.

Tip: Put the code in a module. Then on each sheet when applying the code to the button simply type this between the 2 given code lines:
Call RunMe_v2



Sub RunMe_v2()
Dim x, qty, fRow, sn As Integer 'Variables declared as numbers

x = 8 'Start at row 8

Do 'start Do loop
    qty = Cells(x, "C").Value 'place qty in memory
    If qty > 0 Then 'go to next row when qty is not >0
        
        fRow = Range("H" & Rows.Count).End(xlUp).Row 'determine the first row in second table based on column H
        Cells(x, "B").Copy Range(Cells(fRow + 1, "H"), Cells(fRow + qty, "H")) 'copy item description and paste below the first row in second table the number of times of qty
        sn = Cells(x, "I") 'place serial number in memory
        For Each cell In Range(Cells(fRow + 1, "G"), Cells(fRow + qty, "G")) 'start For Each loop for the same range as the item description
            cell.Value = sn 'place serial number in cell
            sn = sn + 1 'increment serial number by 1
        Next cell 'go to the next cell in the For Each loop
    End If 'code will continue here when qty is not >0
    x = x + 1 'increase row number by 1 for the Do loop
Loop Until Cells(x, "B").Value = vbNullString 'Do loop will stop when Excel finds a row without data in column B

End Sub


Let me know how this works out for you.

Best regards,
Trowa
0

Didn't find the answer you are looking for?

Ask a question
Coley925
Posts
10
Registration date
Tuesday March 22, 2016
Status
Member
Last seen
March 31, 2016

Mar 31, 2016 at 02:46 PM
I added the macro, and it almost works! I played around with it a lot and figured out what the issue is. When the number is more than 5 digits it does not recognize it and gives me an error. I am not sure if this will help, but our serial numbers are always 10 digits. Also, is it possible to have the macro paste the description as "special values" so that my formatting in table2 remains the same.

You are AWESOME!!!



0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Apr 4, 2016 at 12:00 PM
OK Coley, take a look at the adjusted code below. It will now work for bigger serial numbers and the description will be pasted as values only (make sure to remove the Bold and Italic format from table 2). Changes can be found on code line 4 and 15.

Sub RunMe_v2()
Dim x, qty, fRow As Integer 'Variables declared as numbers which reserves memory for numbers between -32,768 and 32,767
Dim sn As Long 'Variable declared as numbers which reserves memory for numbers between -2,147,483,648 and 2,147,483,647
x = 8 'Start at row 8

Do 'start Do loop
    qty = Cells(x, "C").Value 'place qty in memory
    If qty > 0 Then 'go to next row when qty is not >0
        
        fRow = Range("H" & Rows.Count).End(xlUp).Row 'determine the first row in second table based on column H
        Cells(x, "B").Copy 'copy item description
        Range(Cells(fRow + 1, "H"), Cells(fRow + qty, "H")).PasteSpecial Paste:=xlPasteValues 'paste below the first row in second table the number of times of qty, values only
        sn = Cells(x, "I") 'place serial number in memory
        For Each cell In Range(Cells(fRow + 1, "G"), Cells(fRow + qty, "G")) 'start For Each loop for the same range as the item description
            cell.Value = sn 'place serial number in cell
            sn = sn + 1 'increment serial number by 1
        Next cell 'go to the next cell in the For Each loop
    End If 'code will continue here when qty is not >0
    x = x + 1 'increase row number by 1 for the Do loop
Loop Until Cells(x, "B").Value = vbNullString 'Do loop will stop when Excel finds a row without data in column B

End Sub


Best regards,
Trowa
0