Difficult Macro

  • Thread starter Thread starter Pluggie
  • Start date Start date
P

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?
 
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).
 
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
 
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
 
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
 
Hi Eric,

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

Thanks very much!!!
 
Back
Top