Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming help
Hello,
I have an Excel file with 2 worksheets. One containing Order Info called "Orders" and another named "Analysis" which contact some fixed data and two columns for automatic fill in from the "Orders" sheet. I would like to create a code or macro when execute, transfer the Order# and Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the Qty into account. so, if the order# has a quantity of 2, then it will fill in two rows for one Order. The sample data is below. Thanks for any help. Analysis sheet: Line Plan Date Actual Date Order# 100 12/9/09 101 12/10/09 102 12/10/09 103 12/11/09 104 12/14/09 105 12/14/09 106 12/15/09 107 12/18/09 108 12/29/09 Orders Sheet: Order# Prod Start Qty 167834 1/22/10 2 172916 2/1/10 2 169001 2/1/10 2 169003 2/2/10 2 RESULT of Analysis sheet: Line Plan Date Actual Date Order# 100 12/9/09 1/22/10 167834 101 12/10/09 1/22/10 167834 102 12/10/09 2/1/10 172916 103 12/11/09 2/1/10 172916 104 12/14/09 2/1/10 169001 105 12/14/09 2/1/10 169001 106 12/15/09 2/2/10 169003 107 12/18/09 2/2/10 169003 108 12/29/09 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming help
Programming exercise, not tested
Sub t() Dim wsa As Worksheet Dim wso As Worksheet Dim rng As Range Dim c As Range Dim lrow As Long Set wso = Worksheets("orders") Set wsa = Worksheets("analysis") lrow = wso.Cells(Rows.Count, 3).End(xlUp).Row Set rng = Range(wso.Cells(2, 3), wso.Cells(lrow, 3)) With wsa For Each c In rng lrow = .Cells(Rows.Count, 3).End(xlUp).Row + 1 .Cells(lrow, 3).Resize(c, 1) = c.Offset(-1, 0) .Cells(lrow, 4).Resize(c, 1) = c.Offset(-2, 0) Next c End With End Sub "Cam" wrote in message ... Hello, I have an Excel file with 2 worksheets. One containing Order Info called "Orders" and another named "Analysis" which contact some fixed data and two columns for automatic fill in from the "Orders" sheet. I would like to create a code or macro when execute, transfer the Order# and Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the Qty into account. so, if the order# has a quantity of 2, then it will fill in two rows for one Order. The sample data is below. Thanks for any help. Analysis sheet: Line Plan Date Actual Date Order# 100 12/9/09 101 12/10/09 102 12/10/09 103 12/11/09 104 12/14/09 105 12/14/09 106 12/15/09 107 12/18/09 108 12/29/09 Orders Sheet: Order# Prod Start Qty 167834 1/22/10 2 172916 2/1/10 2 169001 2/1/10 2 169003 2/2/10 2 RESULT of Analysis sheet: Line Plan Date Actual Date Order# 100 12/9/09 1/22/10 167834 101 12/10/09 1/22/10 167834 102 12/10/09 2/1/10 172916 103 12/11/09 2/1/10 172916 104 12/14/09 2/1/10 169001 105 12/14/09 2/1/10 169001 106 12/15/09 2/2/10 169003 107 12/18/09 2/2/10 169003 108 12/29/09 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Programming help
Hi KC,
I tried your code and got this error when I ran the macro. "Run-time error '1004': Application-defined or object-defined error" And the Analysis sheet returns like this: Line Plan Date Actual Date Order# 100 12/9/09 Qty 101 12/10/09 Qty 102 12/10/09 103 12/11/09 104 12/14/09 105 12/14/09 106 12/15/09 107 12/18/09 108 12/29/09 What is causing this? Thanks "KC" wrote: Programming exercise, not tested Sub t() Dim wsa As Worksheet Dim wso As Worksheet Dim rng As Range Dim c As Range Dim lrow As Long Set wso = Worksheets("orders") Set wsa = Worksheets("analysis") lrow = wso.Cells(Rows.Count, 3).End(xlUp).Row Set rng = Range(wso.Cells(2, 3), wso.Cells(lrow, 3)) With wsa For Each c In rng lrow = .Cells(Rows.Count, 3).End(xlUp).Row + 1 .Cells(lrow, 3).Resize(c, 1) = c.Offset(-1, 0) .Cells(lrow, 4).Resize(c, 1) = c.Offset(-2, 0) Next c End With End Sub "Cam" wrote in message ... Hello, I have an Excel file with 2 worksheets. One containing Order Info called "Orders" and another named "Analysis" which contact some fixed data and two columns for automatic fill in from the "Orders" sheet. I would like to create a code or macro when execute, transfer the Order# and Prod Start columns from the "Orders" sheet to "Analysis" sheet taken the Qty into account. so, if the order# has a quantity of 2, then it will fill in two rows for one Order. The sample data is below. Thanks for any help. Analysis sheet: Line Plan Date Actual Date Order# 100 12/9/09 101 12/10/09 102 12/10/09 103 12/11/09 104 12/14/09 105 12/14/09 106 12/15/09 107 12/18/09 108 12/29/09 Orders Sheet: Order# Prod Start Qty 167834 1/22/10 2 172916 2/1/10 2 169001 2/1/10 2 169003 2/2/10 2 RESULT of Analysis sheet: Line Plan Date Actual Date Order# 100 12/9/09 1/22/10 167834 101 12/10/09 1/22/10 167834 102 12/10/09 2/1/10 172916 103 12/11/09 2/1/10 172916 104 12/14/09 2/1/10 169001 105 12/14/09 2/1/10 169001 106 12/15/09 2/2/10 169003 107 12/18/09 2/2/10 169003 108 12/29/09 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programming Min and Max | Excel Programming | |||
programming | Excel Programming | |||
programming | Excel Programming | |||
How to add via programming ? | Excel Programming | |||
Do I need programming for this? | Excel Programming |