Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make button to print Multiple occurences of item based on input
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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make button to print Multiple occurences of item based on input
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" wrote in message ... 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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make button to print Multiple occurences of item based on input
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" wrote in message ... 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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make button to print Multiple occurences of item based on input
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make button to print Multiple occurences of item based on input
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" wrote in message ... 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I put button on spreadsheet to make it print a specific part? | Excel Worksheet Functions | |||
Can I make a picture change based on input from a user? | Excel Discussion (Misc queries) | |||
pull data from multiple sheets based on input | Excel Worksheet Functions | |||
Make a 'Print button' | New Users to Excel | |||
NEED TO MAKE THE PRINT BUTTON FUNCTION IN WORKSHEET | Excel Worksheet Functions |