VBA problem: For … next loop with 2 matrices

[RonEmm]

Dear experts,


I have tried to create a VBA code that would pick up information form matrix 2 and store that in matrix 1 (the actual report). The first variable in matrix 1 is a date (column A, the second one is a unique fleet number (column B). What I want is the stops for each fleet number per day in matrix 1 from column C of matrix 2. If the fleet number is not available for a certain day the cost is 0.
The information in matrix 2 comes from a different source than the info in matrix 1.

Initially I created a range in matrix 1 containing the cells to evaluate in the first loop, and also created a range in matrix to that would evaluate the fleetnumber. Like this:

<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="616" width="529"><tbody><tr style="height: 12.75pt;" height="17"><td colspan="3" class="xl66" style="height: 12.75pt; width: 192pt;" height="17" width="256">Matrix 1</td> <td colspan="3" class="xl66" style="width: 144pt;" width="192">Matrix 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl63" style="height: 12.75pt;" height="17">Date</td> <td class="xl63">Fl.nr</td> <td class="xl68">Stops</td> <td class="xl63">Date</td> <td class="xl63">Fl.nr</td> <td class="xl64">Stops</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">100</td> <td class="xl67">Info from matrix 2</td> <td align="right">1</td> <td align="right">100</td> <td class="xl65" align="right">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">101</td> <td class="xl67">Info from matrix 2</td> <td align="right">1</td> <td align="right">101</td> <td class="xl65" align="right">25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">102</td> <td class="xl67">Info from matrix 2</td> <td align="right">1</td> <td align="right">102</td> <td class="xl65" align="right">26</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td align="right">103</td> <td class="xl67">Info from matrix 2</td> <td align="right">2</td> <td align="right">101</td> <td class="xl65" align="right">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2</td> <td align="right">100</td> <td class="xl67">Info from matrix 2</td> <td align="right">2</td> <td align="right">102</td> <td class="xl65" align="right">70</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2</td> <td align="right">101</td> <td class="xl67">Info from matrix 2</td> <td align="right">3</td> <td align="right">103</td> <td class="xl65" align="right">30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2</td> <td align="right">102</td> <td class="xl67">Info from matrix 2</td> <td align="right">3</td> <td align="right">100</td> <td class="xl65" align="right">23</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2</td> <td align="right">103</td> <td class="xl67">Info from matrix 2</td> <td align="right">3</td> <td align="right">101</td> <td class="xl65" align="right">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">3</td> <td align="right">100</td> <td class="xl67">Info from matrix 2</td> <td align="right">4</td> <td align="right">103</td> <td class="xl65" align="right">18</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">3</td> <td align="right">101</td> <td class="xl67">Info from matrix 2</td> <td align="right">4</td> <td align="right">101</td> <td class="xl65" align="right">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">3</td> <td align="right">102</td> <td class="xl67">Info from matrix 2</td> <td align="right">4</td> <td align="right">102</td> <td class="xl65" align="right">21</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">3</td> <td align="right">103</td> <td class="xl67">Info from matrix 2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">4</td> <td align="right">100</td> <td class="xl67">Info from matrix 2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">4</td> <td align="right">101</td> <td class="xl67">Info from matrix 2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">4</td> <td align="right">102</td> <td class="xl67">Info from matrix 2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">4</td> <td align="right">103</td> <td class="xl67">Info from matrix 2</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>


I thought of the loop (abbreviated):
for each n in r1
for each p in r2
(code)
next p
next n


but this doesn't work. Of course the total program is larger than shown here.


I know I am almost there but I can't figure out the last piece of code.


Thanks for your help.

 

[hiker95]

RonEmm,

I hope this helps - a non VBA solution.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Matrix 1					Matrix 2
2	Date	Fl.nr	Stops			Date	Fl.nr	Stops
3	1	100	20			1	100	20
4	1	101	25			1	101	25
5	1	102	26			1	102	26
6	1	103	0			2	101	10
7	2	100	0			2	102	70
8	2	101	10			3	103	30
9	2	102	70			3	100	23
10	2	103	0			3	101	12
11	3	100	23			4	103	18
12	3	101	12			4	101	19
13	3	102	0			4	102	21
14	3	103	30
15	4	100	0
16	4	101	19
17	4	102	21
18	4	103	18
19
Sheet1

The formula in cell C3 copied down:
=SUMPRODUCT(--($F$3:$F$1000=$A3),--($G$3:$G$1000=$B3),$H$3:$H$1000)

 

[RonEmm]

Hiker95,

Thanks for your reply. I should have indicated that I already thought of this solution. The problem though is that yearly, I wil get about 6,000 rows. Next to the "stops" I have 2 more columns that need to be filled. That would mean around 18,000- 20,000 formula's. That would slow down the performance too much and is the reason that I am looking for a VBA solution.

Regards,
Ronald

 

[hiker95]

RonEmm,

Before the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Matrix 1					Matrix 2
2	Date	Fl.nr	Stops			Date	Fl.nr	Stops
3	1	100				1	100	20
4	1	101				1	101	25
5	1	102				1	102	26
6	1	103				2	101	10
7	2	100				2	102	70
8	2	101				3	103	30
9	2	102				3	100	23
10	2	103				3	101	12
11	3	100				4	103	18
12	3	101				4	101	19
13	3	102				4	102	21
14	3	103
15	4	100
16	4	101
17	4	102
18	4	103
19
Sheet1

[/b]After the macro:[/b]

Excel Workbook
	A	B	C	D	E	F	G	H
1	Matrix 1					Matrix 2
2	Date	Fl.nr	Stops			Date	Fl.nr	Stops
3	1	100	20			1	100	20
4	1	101	25			1	101	25
5	1	102	26			1	102	26
6	1	103	0			2	101	10
7	2	100	0			2	102	70
8	2	101	10			3	103	30
9	2	102	70			3	100	23
10	2	103	0			3	101	12
11	3	100	23			4	103	18
12	3	101	12			4	101	19
13	3	102	0			4	102	21
14	3	103	30
15	4	100	0
16	4	101	19
17	4	102	21
18	4	103	18
19
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Sub FindStops()
Dim LR As Long, LR2 As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
LR2 = Cells(Rows.Count, "F").End(xlUp).Row
With Range("C3:C" & LR)
  .FormulaR1C1 = "=SUMPRODUCT(--(R3C6:R" & LR2 & "C6=RC1),--(R3C7:R" & LR2 & "C7=RC2),R3C8:R" & LR2 & "C8)"
  .Value = .Value
End With
End Sub

Then run the "FindStops" Macro.


If the data displayed above does not match your workbook/worksheet, then please post a screenshot of your data.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[RonEmm]

Hiker95,

Thanks for this solution. I hadn't thought of this possibility. I have had not the time yet to try it but I will soon. I'll let you know.

The only thing is that I believe that there should be a more efficient way of programming this.

By the way: how do I copy screenshots into my messages like you did? I tried to to this with the program "printkey" but that won't work.

Regards and thanks again!

 

[hiker95]

RonEmm,

By the way: how do I copy screenshots into my messages like you did?

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php