Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Macro
Hi, I have 3 sheets. Sheet 1 contains a 1-Column list of Customer_ID's Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions Sheet 3 is empty. The result of the macro I want is (in Sheet 3) A 3-Column list of Customer_ID, Product_ID and Product_Description where each customer has a full set of products. Unfortunately, the list of customers, aswell as the list of products will vary in length each time the macro has to be run. How do I do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Macro
I am obviously missing something, but what links the Customer_ID's on Sheet1 with the Product_ID's/Product_Descriptions? I'm guessing there is other information on the sheets that you have not told us about... I think we need to know that information (at least as it relates to linking the customers with the products). -- Rick (MVP - Excel) "Pluggie" wrote in message ... Hi, I have 3 sheets. Sheet 1 contains a 1-Column list of Customer_ID's Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions Sheet 3 is empty. The result of the macro I want is (in Sheet 3) A 3-Column list of Customer_ID, Product_ID and Product_Description where each customer has a full set of products. Unfortunately, the list of customers, aswell as the list of products will vary in length each time the macro has to be run. How do I do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Macro
There is no other link and no other information. The information on sheet 1 and sheet 2 is imported by another macro before running this one. So every Customer needs to get the full set of products each time the macro is run. Example: Sheet 1: Customer_ID 1 2 3 Sheet 2: ProductID Product_Descr 9 Book 8 Chair Macro that makes sheet 3: Customer_ID ProductID ProductDescr 1 8 Chair 1 9 Book 2 8 Chair 2 9 Book 3 8 Chair 3 9 Book "Rick Rothstein" wrote: I am obviously missing something, but what links the Customer_ID's on Sheet1 with the Product_ID's/Product_Descriptions? I'm guessing there is other information on the sheets that you have not told us about... I think we need to know that information (at least as it relates to linking the customers with the products). -- Rick (MVP - Excel) "Pluggie" wrote in message ... Hi, I have 3 sheets. Sheet 1 contains a 1-Column list of Customer_ID's Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions Sheet 3 is empty. The result of the macro I want is (in Sheet 3) A 3-Column list of Customer_ID, Product_ID and Product_Description where each customer has a full set of products. Unfortunately, the list of customers, aswell as the list of products will vary in length each time the macro has to be run. How do I do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Macro
This should do what you're asking for:
Sub Order_List() Dim nCust As Long, nProd As Long ' nCust = ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1).CurrentRegion.Rows.Count - 1 nProd = ActiveWorkbook.Worksheets("Sheet2").Cells(1, 1).CurrentRegion.Rows.Count - 1 ' Application.ScreenUpdating = False ' ActiveWorkbook.Worksheets("Sheet3").Cells(1, 1) = _ ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells(1, 2) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells(1, 3) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(1, 2) For i = 1 To nCust For j = 1 To nProd ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1, 1) = _ ActiveWorkbook.Worksheets("Sheet1").Cells(i + 1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1, 2) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1, 3) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 2) Next j Next i ' ActiveWorkbook.Worksheets("Sheet3").Select Application.ScreenUpdating = True ' End Sub HTH, Eric "Pluggie" wrote: Hi, I have 3 sheets. Sheet 1 contains a 1-Column list of Customer_ID's Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions Sheet 3 is empty. The result of the macro I want is (in Sheet 3) A 3-Column list of Customer_ID, Product_ID and Product_Description where each customer has a full set of products. Unfortunately, the list of customers, aswell as the list of products will vary in length each time the macro has to be run. How do I do this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Macro
I think this will do what you have asked for... Sub CombineCustomersWithProducts() Dim R As Range Dim X As Long, Z As Long Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet Dim LastRow1 As Long, LastRow2 As Long, LastRow3 As Long Set WS1 = Worksheets("Sheet1") Set WS2 = Worksheets("Sheet2") Set WS3 = Worksheets("Sheet3") LastRow1 = WS1.Cells(WS1.Rows.Count, "A").End(xlUp).Row LastRow2 = WS2.Cells(WS1.Rows.Count, "A").End(xlUp).Row Set R = WS2.Range("A2:B" & LastRow2) WS3.Range("A1").Value = WS1.Range("A1").Value WS2.Range("A1:B1").Copy WS3.Range("B1") For X = 2 To LastRow1 LastRow3 = WS3.Cells(WS1.Rows.Count, "A").End(xlUp).Row For Z = LastRow3 + 1 To LastRow3 + R.Rows.Count WS3.Cells(Z, "A").Value = WS1.Cells(X, "A").Value Next R.Copy WS3.Cells(LastRow3 + 1, "B") Next End Sub -- Rick (MVP - Excel) "Pluggie" wrote in message ... There is no other link and no other information. The information on sheet 1 and sheet 2 is imported by another macro before running this one. So every Customer needs to get the full set of products each time the macro is run. Example: Sheet 1: Customer_ID 1 2 3 Sheet 2: ProductID Product_Descr 9 Book 8 Chair Macro that makes sheet 3: Customer_ID ProductID ProductDescr 1 8 Chair 1 9 Book 2 8 Chair 2 9 Book 3 8 Chair 3 9 Book "Rick Rothstein" wrote: I am obviously missing something, but what links the Customer_ID's on Sheet1 with the Product_ID's/Product_Descriptions? I'm guessing there is other information on the sheets that you have not told us about... I think we need to know that information (at least as it relates to linking the customers with the products). -- Rick (MVP - Excel) "Pluggie" wrote in message ... Hi, I have 3 sheets. Sheet 1 contains a 1-Column list of Customer_ID's Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions Sheet 3 is empty. The result of the macro I want is (in Sheet 3) A 3-Column list of Customer_ID, Product_ID and Product_Description where each customer has a full set of products. Unfortunately, the list of customers, aswell as the list of products will vary in length each time the macro has to be run. How do I do this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficult Macro
Hi Eric, Tried it... had to delete your quotes from the code first and it worked. Thanks very much!!! "EricG" wrote: This should do what you're asking for: Sub Order_List() Dim nCust As Long, nProd As Long ' nCust = ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1).CurrentRegion.Rows.Count - 1 nProd = ActiveWorkbook.Worksheets("Sheet2").Cells(1, 1).CurrentRegion.Rows.Count - 1 ' Application.ScreenUpdating = False ' ActiveWorkbook.Worksheets("Sheet3").Cells(1, 1) = _ ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells(1, 2) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells(1, 3) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(1, 2) For i = 1 To nCust For j = 1 To nProd ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1, 1) = _ ActiveWorkbook.Worksheets("Sheet1").Cells(i + 1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1, 2) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 1) ActiveWorkbook.Worksheets("Sheet3").Cells((i - 1) * nProd + j + 1, 3) = _ ActiveWorkbook.Worksheets("Sheet2").Cells(j + 1, 2) Next j Next i ' ActiveWorkbook.Worksheets("Sheet3").Select Application.ScreenUpdating = True ' End Sub HTH, Eric "Pluggie" wrote: Hi, I have 3 sheets. Sheet 1 contains a 1-Column list of Customer_ID's Sheet 2 contains a 2-Column list of Product_ID's and Product_Descriptions Sheet 3 is empty. The result of the macro I want is (in Sheet 3) A 3-Column list of Customer_ID, Product_ID and Product_Description where each customer has a full set of products. Unfortunately, the list of customers, aswell as the list of products will vary in length each time the macro has to be run. How do I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficult Time/ Deleting Macro/ Tool Bar | New Users to Excel | |||
macro creation to format mulitple rows in a list *difficult* | Excel Programming | |||
Too difficult for me, please help. | Excel Worksheet Functions | |||
Difficult macro to calculate and format data | Excel Programming | |||
difficult concatenate macro | Excel Programming |