CostCenter and Product combinations

[?Mark]

Hi,

I need to create macro which should create all combinations of three products (listed below) and cost centers. Number of cost centers vary monthly. All cost centers are 5 digits long and those are on sheet "CostCenters" in column "A". Macro should create combinations to sheet "CostcenterAndProduct". In that sheet should be cost center in column A and product in column B.

Product1 = 5542
Product2 = 8372
Product3 = 1837

Can some one please help me with this? Is this even possible?

Thanks in advance for Your help!

[Simon Lloyd]

It's impossible without more info from you, how will you associate which product should belong to which cost centre? if you simply wanted to output:
Product1 =5542
Product2 = 5542
Product3 = 5542

Product1 = 8372
Product2 = 8372
Product3 = 8372

Product......etc

Then that is very easy to do, give us more info and we can help more.

[?Mark]

Let's say I have three cost center:

CC1 = 11111
CC2 = 22222
CC3 = 33333

Number of cost center vary and they are on separate sheet "CostCenters".

And in this case result should be like this on sheet "CostcenterAndProduct":

11111 5542
11111 8372
11111 1837
22222 5542
22222 8372
22222 1837
33333 5542
33333 1837
33333 8372

I have tried to do this by using "Do until.... Loop" but it does not work. Also products are "marked" in the beginning of my macro like:
Pro1 = "5542"
Pro2 = "1837"
Pro3 = "8372"

There is less product than cost center so that's why I decided to number those products like that is it a good way?

Is it somehow possible tell macro that product name has always name: Fixed word "Pro" and changing number from 1 to 3 in this case or how this should be done.

If you need more information please contact. Thanks!

[mohanvijay]

can you please attach sample workbook with the result that you need?

[?Mark]

Hi,

Please find attached example workbook with macro below:

Problem is that I have to create three times in this case same thing. How I could do this easier.

Thanks!


Sub test()
Pro1 = "5542"
Pro2 = "1837"
Pro3 = "8372"
Sheets("CostCenters").Select
Range("A2").Select
Selection.End(xlDown).Select
NroOfCC = ActiveCell.Row - 1
Range("A2").Select
Start = 0
Do Until Start = NroOfCC
Sheets("CostCenters").Select
Range("A2").Select
ActiveCell.Offset(Start, 0).Select
CC = ActiveCell.Value
Sheets("CostCentersAndProducts").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CC
ActiveCell.Offset(0, 1).Value = Pro1
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CC
ActiveCell.Offset(0, 1).Value = Pro2
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CC
ActiveCell.Offset(0, 1).Value = Pro3
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Select
Start = Start + 1
Loop
End Sub

[mohanvijay]

try this



Sub test1()
Dim Last_Rw As Long
Dim Ct_Rw As Long
Dim i As Long
Dim T_Val As Long
Dim Pro(2) As Variant
Pro(0) = "5542"
Pro(1) = "1837"
Pro(2) = "8372"
Ct_Rw = 3
Last_Rw = ThisWorkbook.Worksheets("CostCenters").Cells(Rows.Count, 1).End(xlUp).Row
With ThisWorkbook.Worksheets("CostCentersAndProducts")
For i = 2 To Last_Rw

T_Val = ThisWorkbook.Worksheets("CostCenters").Cells(i, 1).Value
.Range("a" & Ct_Rw & ":a" & Ct_Rw + 2).Value = T_Val
.Range("b" & Ct_Rw & ":b" & Ct_Rw + 2) = Application.Transpose(Pro)
Ct_Rw = Ct_Rw + 3
Next i
End With



End Sub

[?Mark]

Thank you really much, Mohanvijay ! This is now solved.