Make button to print Multiple occurences of item based on input

  • Thread starter Thread starter CarpeDiemFL
  • Start date Start date
C

CarpeDiemFL

Hi all and thanks for such an AMAZING site! You've vicariously gotten me
out of several programming jams in the past, and I'm now a little
embarrassed to find myself between a code and a hard place.

I am writing (trying, anyway) VBA to do the following:

Once a macro is executed, excel looks at rows that have the following
information:

ColA ColB ColC ColD
ITEM# SLOT# DESCRIPTION # of Labels
5301 DF212 Black Beans 5
1624 CA172 Rice 2

I need to tell excel to (on a different sheet) create:

5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
1624
Rice
CA172
1624
Rice
CA172

...and so on. In other words, I need it to repeat the insert of the
Item#, Slot# and Description specified by what is entered for the #of
Labels.

Having been a great fan of this site for some time now, I know better
than to ask for a simple "Do it for me". Getting there is half the fun,
so they say. Any help or pointing in the right direction or hints or
clues or web site references would be incredibly appreciated.

Thanks again for such an awesome forum!

Jim C.
 
Jim,

Try the following code:

Sub AAA()

Dim SourceRng As Range
Dim Rng As Range
Dim DestRng As Range
Dim N As Long
With Worksheets("Sheet1")
Set SourceRng = .Range(.Range("A2"), .Cells(Rows.Count,
"A").End(xlUp))
End With
Set DestRng = Worksheets("Sheet2").Range("A1")
For Each Rng In SourceRng
For N = 1 To Rng.EntireRow.Cells(1, "D").Value
DestRng(1, 1).Value = Rng.EntireRow.Cells(1, "A")
DestRng(2, 1).Value = Rng.EntireRow.Cells(1, "C")
DestRng(3, 1).Value = Rng.EntireRow.Cells(1, "B")
Set DestRng = DestRng(4, 1)
Next N
Next Rng

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



CarpeDiemFL said:
Hi all and thanks for such an AMAZING site! You've vicariously gotten me
out of several programming jams in the past, and I'm now a little
embarrassed to find myself between a code and a hard place.

I am writing (trying, anyway) VBA to do the following:

Once a macro is executed, excel looks at rows that have the following
information:

ColA ColB ColC ColD
ITEM# SLOT# DESCRIPTION # of Labels
5301 DF212 Black Beans 5
1624 CA172 Rice 2

I need to tell excel to (on a different sheet) create:

5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
1624
Rice
CA172
1624
Rice
CA172

..and so on. In other words, I need it to repeat the insert of the
Item#, Slot# and Description specified by what is entered for the #of
Labels.

Having been a great fan of this site for some time now, I know better
than to ask for a simple "Do it for me". Getting there is half the fun,
so they say. Any help or pointing in the right direction or hints or
clues or web site references would be incredibly appreciated.

Thanks again for such an awesome forum!

Jim C.


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide
Click to expand...
to creating financial statements
 
Sub Tester1()
Dim rng As Range, Cell As Range
Dim rw As Long, i As Long
Dim shDest As Worksheet
Set shDest = Worksheets("Sheet2")
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rw = 1
For Each Cell In rng
For i = 1 To Cell.Offset(0, 3).Value
shDest.Cells(rw, 1).Value = Cell.Value
shDest.Cells(rw + 1, 1).Value = Cell.Offset(0, 2).Value
shDest.Cells(rw + 2, 1).Value = Cell.Offset(0, 1).Value
rw = rw + 3
Next i
Next Cell
End Sub





--
Regards,
Tom Ogilvy


CarpeDiemFL said:
Hi all and thanks for such an AMAZING site! You've vicariously gotten me
out of several programming jams in the past, and I'm now a little
embarrassed to find myself between a code and a hard place.

I am writing (trying, anyway) VBA to do the following:

Once a macro is executed, excel looks at rows that have the following
information:

ColA ColB ColC ColD
ITEM# SLOT# DESCRIPTION # of Labels
5301 DF212 Black Beans 5
1624 CA172 Rice 2

I need to tell excel to (on a different sheet) create:

5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
5301
Black Beans
DF212
1624
Rice
CA172
1624
Rice
CA172

..and so on. In other words, I need it to repeat the insert of the
Item#, Slot# and Description specified by what is entered for the #of
Labels.

Having been a great fan of this site for some time now, I know better
than to ask for a simple "Do it for me". Getting there is half the fun,
so they say. Any help or pointing in the right direction or hints or
clues or web site references would be incredibly appreciated.

Thanks again for such an awesome forum!

Jim C.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Tom-Your solution to my problem was AMAZING and worked PERFECTLY. On
favor, however. I might be able to figure this out on my own, but
really don't want to screw up your awesome code. The output right no
is giving me:

Item Number
Slot Number
Description
Item Number
Slot Number
Description

Or, to refer back to my example from my original post:

5301
DF212
Black Beans
5301
DF212
Black Beans

...etc., etc.

What would I need to change to have it list in the following order:

Item Number
Description
Slot Number

Or, again, referring to my original post:

5301
Black Beans
DF212

Thanks again for your incredible solution to this nightmare!!!!

Jim Carpente
 
Based on your original post and copying the data from your email and pasting
it into Excel starting in Cell A1 of Sheet1:

Sub Tester1()
Dim rng As Range, Cell As Range
Dim rw As Long, i As Long
Dim shDest As Worksheet
Set shDest = Worksheets("Sheet2")
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rw = 1
For Each Cell In rng
For i = 1 To Cell.Offset(0, 3).Value
shDest.Cells(rw, 1).Value = Cell.Value
shDest.Cells(rw + 1, 1).Value = Cell.Offset(0, 2).Value
shDest.Cells(rw + 2, 1).Value = Cell.Offset(0, 1).Value
rw = rw + 3
Next i
Next Cell
End Sub

produces:

5301
Black Beans
DF212

-------------

Sub Tester1()
Dim rng As Range, Cell As Range
Dim rw As Long, i As Long
Dim shDest As Worksheet
Set shDest = Worksheets("Sheet2")
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
rw = 1
For Each Cell In rng
For i = 1 To Cell.Offset(0, 3).Value
shDest.Cells(rw, 1).Value = Cell.Value
shDest.Cells(rw + 1, 1).Value = Cell.Offset(0, 1).Value '<==
shDest.Cells(rw + 2, 1).Value = Cell.Offset(0, 2).Value '<==
rw = rw + 3
Next i
Next Cell
End Sub

Produces:

5301
DF212
Black Beans



--
Regards,
Tom Ogilvy


CarpeDiemFL said:
Tom-Your solution to my problem was AMAZING and worked PERFECTLY. One
favor, however. I might be able to figure this out on my own, but I
really don't want to screw up your awesome code. The output right now
is giving me:

Item Number
Slot Number
Description
Item Number
Slot Number
Description

Or, to refer back to my example from my original post:

5301
DF212
Black Beans
5301
DF212
Black Beans

..etc., etc.

What would I need to change to have it list in the following order:

Item Number
Description
Slot Number

Or, again, referring to my original post:

5301
Black Beans
DF212

Thanks again for your incredible solution to this nightmare!!!!

Jim Carpenter


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Back
Top