Multiply each row with another all rows

[motilulla]

Hello,</SPAN></SPAN>

I got some results in the columns C:E in this example there are 4 rows</SPAN></SPAN>
And some results in the columns G:I in this example there are 6 rows</SPAN></SPAN>
I need to multiply each row of Column C:E with all rows of column G:I so it will be 4*6=24 rows as shown the result into columns L:Q </SPAN></SPAN>
Note: if possible I want alternate font colours red and blue in the result area as per multiplied results. </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5			n1	n2	n3		n4	n5	n6			n1	n2	n3	n4	n5	n6
6			36	10	6		23	25	2			36	10	6	23	25	2
7			35	33	26		3	19	40			36	10	6	3	19	40
8			2	10	30		43	8	49			36	10	6	43	8	49
9			43	38	19		9	1	5			36	10	6	9	1	5
10							5	43	6			36	10	6	5	43	6
11							24	38	3			36	10	6	24	38	3
12												35	33	26	23	25	2
13												35	33	26	3	19	40
14												35	33	26	43	8	49
15												35	33	26	9	1	5
16												35	33	26	5	43	6
17												35	33	26	24	38	3
18												2	10	30	23	25	2
19												2	10	30	3	19	40
20												2	10	30	43	8	49
21												2	10	30	9	1	5
22												2	10	30	5	43	6
23												2	10	30	24	38	3
24												43	38	19	23	25	2
25												43	38	19	3	19	40
26												43	38	19	43	8	49
27												43	38	19	9	1	5
28												43	38	19	5	43	6
29												43	38	19	24	38	3
30
31
32
33
Sheet4

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Jul32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rng1 [COLOR="Navy"]As[/COLOR] Range, Dn1 [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Col2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng1 = Range("G6", Range("G" & Rows.Count).End(xlUp))
c = 5: Col1 = 5: Col2 = 3
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   Col1 = IIf(Col1 = 3, 5, 3)
   Col2 = IIf(Col1 = 5, 3, 5)
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn1 [COLOR="Navy"]In[/COLOR] Rng1
     c = c + 1
     Cells(c, 12).Resize(, 3).Value = Dn.Resize(, 3).Value
     Cells(c, 12).Resize(, 3).Font.ColorIndex = Col1
     Cells(c, 15).Resize(, 3).Value = Dn1.Resize(, 3).Value
     Cells(c, 15).Resize(, 3).Font.ColorIndex = Col2

   [COLOR="Navy"]Next[/COLOR] Dn1
[COLOR="Navy"]Next[/COLOR] Dn
Range("L1").Resize(c, 6).Font.Bold = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[motilulla]

Try this:-

[COLOR=navy]Sub[/COLOR] MG26Jul32
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Rng1 [COLOR=navy]As[/COLOR] Range, Dn1 [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Col1 [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Col2 [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("C6", Range("C" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Rng1 = Range("G6", Range("G" & Rows.Count).End(xlUp))
c = 5: Col1 = 5: Col2 = 3
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
   Col1 = IIf(Col1 = 3, 5, 3)
   Col2 = IIf(Col1 = 5, 3, 5)
   [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn1 [COLOR=navy]In[/COLOR] Rng1
     c = c + 1
     Cells(c, 12).Resize(, 3).Value = Dn.Resize(, 3).Value
     Cells(c, 12).Resize(, 3).Font.ColorIndex = Col1
     Cells(c, 15).Resize(, 3).Value = Dn1.Resize(, 3).Value
     Cells(c, 15).Resize(, 3).Font.ColorIndex = Col2

   [COLOR=navy]Next[/COLOR] Dn1
[COLOR=navy]Next[/COLOR] Dn
Range("L1").Resize(c, 6).Font.Bold = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

MickG, I did check what I got max rows 280 one side and 224 other side 280*224= 62720 it worked really perfect and alternate font colouring is fine too.</SPAN></SPAN>

I appreciate your prompt replay. Have a good weekend </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>