Hi there,
I have the following code which compares a string between two sheets. First sheet is 'data', second is 'saw'. I'm trying to copy an array of numbers (energy consumption for each hour of the day) based on machine ID and date (hence the compare string). Can someone help modify it so it will copy the array of 24 number, not just the first number?
I have a few worksheets 'saw', drill, lathe. So I am using ActiveSheet, so I can run the same macro as all data is in the 'data' worksheet. Hope someone can help
Sub test_copy()
Dim a, i As Long, txt As String
a = Sheets("data").Range("a1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(a, 1)
txt = a(i, 2) & ";;" & a(i, 3)
.Item(txt) = a(i, 4)
Next
a = ActiveSheet.Range("a1").CurrentRegion.Resize(, 4).Value
For i = 1 To UBound(a, 1)
txt = a(i, 1) & ";;" & a(i, 2)
If .exists(txt) Then
a(i, 4) = .Item(txt)
Else
a(i, 4) = ""
End If
Next
End With
ActiveSheet.Range("a1").CurrentRegion.Resize(, 4).Value = a
End Sub
--------------------
datasheet below
--------------------
Excel 2007
<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
-----------------------
machine sheet
-----------------------
Excel 2007
<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
cheers
I have the following code which compares a string between two sheets. First sheet is 'data', second is 'saw'. I'm trying to copy an array of numbers (energy consumption for each hour of the day) based on machine ID and date (hence the compare string). Can someone help modify it so it will copy the array of 24 number, not just the first number?
I have a few worksheets 'saw', drill, lathe. So I am using ActiveSheet, so I can run the same macro as all data is in the 'data' worksheet. Hope someone can help
Sub test_copy()
Dim a, i As Long, txt As String
a = Sheets("data").Range("a1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(a, 1)
txt = a(i, 2) & ";;" & a(i, 3)
.Item(txt) = a(i, 4)
Next
a = ActiveSheet.Range("a1").CurrentRegion.Resize(, 4).Value
For i = 1 To UBound(a, 1)
txt = a(i, 1) & ";;" & a(i, 2)
If .exists(txt) Then
a(i, 4) = .Item(txt)
Else
a(i, 4) = ""
End If
Next
End With
ActiveSheet.Range("a1").CurrentRegion.Resize(, 4).Value = a
End Sub
--------------------
datasheet below
--------------------
Excel 2007
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | sawing machine | |||||||||||||
2 | 100569 | 01/10/2012 | 0.1 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
3 | 100569 | 02/10/2012 | 0.2 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
4 | 100569 | 03/10/2012 | 0.3 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
5 | 100569 | 04/10/2012 | 0.4 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
6 | 100569 | 05/10/2012 | 0.5 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
7 | 100569 | 06/10/2012 | 0.6 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
8 | 100569 | 07/10/2012 | 0.7 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
9 | 100569 | 08/10/2012 | 0.8 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
10 | 100569 | 09/10/2012 | 0.9 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
11 | 100569 | 10/10/2012 | 1 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
12 | 100569 | 11/10/2012 | 1.1 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
13 | 100569 | 12/10/2012 | 1.2 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
14 | 100569 | 13/10/2012 | 1.3 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
15 | 100569 | 14/10/2012 | 1.4 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
16 | 100569 | 15/10/2012 | 1.5 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
17 | 100569 | 16/10/2012 | 1.6 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
18 | 100569 | 17/10/2012 | 1.7 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
19 | 100569 | 18/10/2012 | 1.8 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
20 | 100569 | 19/10/2012 | 1.9 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
21 | 100569 | 20/10/2012 | 2 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
22 | 100569 | 21/10/2012 | 2.1 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
23 | 100569 | 22/10/2012 | 2.2 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
24 | lathe machine | |||||||||||||
25 | 100256 | 01/10/2012 | 3.1 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
26 | 100256 | 02/10/2012 | 3.2 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
27 | 100256 | 03/10/2012 | 3.3 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
28 | 100256 | 04/10/2012 | 3.4 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
29 | 100256 | 05/10/2012 | 3.5 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
30 | 100256 | 06/10/2012 | 3.6 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
31 | 100256 | 07/10/2012 | 3.7 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
32 | 100256 | 08/10/2012 | 3.8 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
33 | 100256 | 09/10/2012 | 3.9 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
34 | 100256 | 10/10/2012 | 4 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
35 | 100256 | 11/10/2012 | 4.1 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
36 | 100256 | 12/10/2012 | 4.2 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
37 | 100256 | 13/10/2012 | 4.3 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
38 | 100256 | 14/10/2012 | 4.4 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
39 | 100256 | 15/10/2012 | 4.5 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
40 | 100256 | 16/10/2012 | 4.6 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
41 | 100256 | 17/10/2012 | 4.7 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
42 | 100256 | 18/10/2012 | 4.8 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
43 | 100256 | 19/10/2012 | 4.9 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
44 | 100256 | 20/10/2012 | 5 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
45 | 100256 | 21/10/2012 | 5.1 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
46 | 100256 | 22/10/2012 | 5.2 | 0.25 | 0.89 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
47 | drilling machine | |||||||||||||
48 | 45896 | 01/10/2012 | 6.1 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
49 | 45896 | 02/10/2012 | 6.2 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
50 | 45896 | 03/10/2012 | 6.3 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
51 | 45896 | 04/10/2012 | 6.4 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
52 | 45896 | 05/10/2012 | 6.5 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
53 | 45896 | 06/10/2012 | 6.6 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
54 | 45896 | 07/10/2012 | 6.7 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
55 | 45896 | 08/10/2012 | 6.8 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
56 | 45896 | 09/10/2012 | 6.9 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
57 | 45896 | 10/10/2012 | 7 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
58 | 45896 | 11/10/2012 | 7.1 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
59 | 45896 | 12/10/2012 | 7.2 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
60 | 45896 | 13/10/2012 | 7.3 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
61 | 45896 | 14/10/2012 | 7.4 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
62 | 45896 | 15/10/2012 | 7.5 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
63 | 45896 | 16/10/2012 | 7.6 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
64 | 45896 | 17/10/2012 | 7.7 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
65 | 45896 | 18/10/2012 | 7.8 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
66 | 45896 | 19/10/2012 | 7.9 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
67 | 45896 | 20/10/2012 | 8 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
68 | 45896 | 21/10/2012 | 8.1 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 | |
69 | 45896 | 22/10/2012 | 8.2 | 1.25 | 2.3 | 0.25 | 0.22 | 0.14 | 0.56 | 0.47 | 0.58 | 0.45 | 0.47 |
<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
data
-----------------------
machine sheet
-----------------------
Excel 2007
A | B | C | |
---|---|---|---|
1 | 100569 | 23/09/2010 | |
2 | 100569 | 24/09/2010 | |
3 | 100569 | 25/09/2010 | |
4 | 100569 | 26/09/2010 | |
5 | 100569 | 27/09/2010 | |
6 | 100569 | 28/09/2010 | |
7 | 100569 | 29/09/2010 | |
8 | 100569 | 30/09/2010 | |
9 | 100569 | 01/10/2010 | |
10 | 100569 | 02/10/2010 | |
11 | 100569 | 03/10/2010 | |
12 | 100569 | 04/10/2010 | |
13 | 100569 | 05/10/2010 | |
14 | 100569 | 06/10/2010 | |
15 | 100569 | 07/10/2010 | |
16 | 100569 | 08/10/2010 | |
17 | 100569 | 09/10/2010 | |
18 | 100569 | 10/10/2010 | |
19 | 100569 | 11/10/2010 | |
20 | 100569 | 12/10/2010 | |
21 | 100569 | 13/10/2010 | |
22 | 100569 | 14/10/2010 | |
23 | 100569 | 15/10/2010 | |
24 | 100569 | 16/10/2010 | |
25 | 100569 | 17/10/2010 | |
26 | 100569 | 18/10/2010 | |
27 | 100569 | 19/10/2010 | |
28 | 100569 | 20/10/2010 | |
29 | 100569 | 21/10/2010 | |
30 | 100569 | 22/10/2010 | |
31 | 100569 | 23/10/2010 | |
32 | 100569 | 24/10/2010 | |
33 | 100569 | 25/10/2010 | |
34 | 100569 | 26/10/2010 | |
35 | 100569 | 27/10/2010 | |
36 | 100569 | 28/10/2010 | |
37 | 100569 | 29/10/2010 | |
38 | 100569 | 30/10/2010 |
<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
saw
cheers