Difficult Macro

[Pluggie]

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?

 

[Rick Rothstein]

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).

 

[Pluggie]

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

 

[EricG]

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

 

[Rick Rothstein]

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

 

[Pluggie]

Hi Eric,

Tried it... had to delete your quotes from the code first and it worked.

Thanks very much!!!