Hello folks,
I am desperate to scan the tables via Vba and determine specific values.
I have a master table (first table) that is automatically generated in a sheet every day.
This table should be compared with the table on the left (here as an example below) and the data from the master table should be transferred to the table on the right (here also as an example below). The following columns are to be compared to transfer the data: Material No - Part number. Also wenn Material Nr in Part Number vorhanden ist, dann soll die Abfrage starten.
I need the number of hours of activity between the first "Set-up time" and last "Cleaning time" of a "Material No." from the machines. This should be entered later in the right-hand table in the "Duration" column.
The period of the requested data should be from 6am yesterday to 6am today. However, some data will go beyond this period, but it should only be calculated/queried for this specified period. That is, "Start time" of activity "Set-up time" of a "Material Nr" must start after 6am yesterday and if for example "End Time" of a "Material Nr" goes up to 8am, then it should be cut at 6am and the duration calculated, regardless of whether "Cleaning time" has been reached or not.
This should be calculated for all "Material Nr" of the machines and the remaining information from the other columns should then be inserted into the right table. The process should happen for all machines, i.e. several tables like the one on the right in the example.
In addition, lines with the material number "-1" that fall within the specified period should be inserted into the table (here as an example at the bottom) on a new sheet. A hyperlink should be inserted in the "Reason for Delay" column in the table on the right (here as an example above). This should also be done for all machines, i.e. several sheets.
Since this has to take place automatically every day, new data should be added to each table below as a new row.
I started a few attempts to program it but without success.
I would be super grateful for any help!
Regards
Jimmy
I am desperate to scan the tables via Vba and determine specific values.
I have a master table (first table) that is automatically generated in a sheet every day.
This table should be compared with the table on the left (here as an example below) and the data from the master table should be transferred to the table on the right (here also as an example below). The following columns are to be compared to transfer the data: Material No - Part number. Also wenn Material Nr in Part Number vorhanden ist, dann soll die Abfrage starten.
I need the number of hours of activity between the first "Set-up time" and last "Cleaning time" of a "Material No." from the machines. This should be entered later in the right-hand table in the "Duration" column.
The period of the requested data should be from 6am yesterday to 6am today. However, some data will go beyond this period, but it should only be calculated/queried for this specified period. That is, "Start time" of activity "Set-up time" of a "Material Nr" must start after 6am yesterday and if for example "End Time" of a "Material Nr" goes up to 8am, then it should be cut at 6am and the duration calculated, regardless of whether "Cleaning time" has been reached or not.
This should be calculated for all "Material Nr" of the machines and the remaining information from the other columns should then be inserted into the right table. The process should happen for all machines, i.e. several tables like the one on the right in the example.
In addition, lines with the material number "-1" that fall within the specified period should be inserted into the table (here as an example at the bottom) on a new sheet. A hyperlink should be inserted in the "Reason for Delay" column in the table on the right (here as an example above). This should also be done for all machines, i.e. several sheets.
Since this has to take place automatically every day, new data should be added to each table below as a new row.
I started a few attempts to program it but without success.
I would be super grateful for any help!
Regards
Jimmy
| Machine | Matchcode2 | Production Order Number | Material Nr | Activity | Start Date | Start Time | End Date | End Time | Machine Time Booking (h) | Quantity 1 | Quantity 2 |
| 12 | -- | ------ | -1 | Repair | 27.07.2022 | 20:13:00 | 27.07.2022 | 20:22:00 | 0,2 | ||
| 12 | 400 | BU2331 | F141689 | Manufacturing time | 27.07.2022 | 05:52:00 | 27.07.2022 | 10:50:00 | 4,8 | 114.592,0 | 229.184,0 |
| 12 | 400 | BU2331 | F141689 | Cleaning time | 27.07.2022 | 10:50:00 | 27.07.2022 | 11:32:00 | 0,7 | ||
| 12 | 400 | BU2332 | F141689 | Set-up time | 27.07.2022 | 11:32:00 | 27.07.2022 | 12:18:00 | 0,8 | ||
| 12 | 400 | BU2332 | F141689 | Running-in time | 27.07.2022 | 12:18:00 | 27.07.2022 | 12:52:00 | 0,6 | ||
| 12 | 400 | BU2332 | F141689 | Manufacturing time | 27.07.2022 | 12:52:00 | 27.07.2022 | 18:15:00 | 5,4 | ||
| 12 | 400 | BU2332 | F141689 | Cleaning time | 27.07.2022 | 18:15:00 | 27.07.2022 | 18:49:00 | 0,6 | ||
| 12 | 400 | BU2333 | F141689 | Set-up time | 27.07.2022 | 18:49:00 | 27.07.2022 | 20:13:00 | 1,4 | ||
| 12 | 400 | BU2333 | F141689 | Running-in time | 27.07.2022 | 20:22:00 | 27.07.2022 | 20:37:00 | 0,3 | ||
| 12 | 400 | BU2333 | F141689 | Manufacturing time | 27.07.2022 | 20:37:00 | 28.07.2022 | 01:23:00 | 4,8 | ||
| 12 | 400 | BU2333 | F141689 | Cleaning time | 28.07.2022 | 01:23:00 | 28.07.2022 | 01:53:00 | 0,5 | ||
| 12 | 400 | BU2334 | F141689 | Set-up time | 28.07.2022 | 01:53:00 | 28.07.2022 | 02:16:00 | 0,4 | ||
| 12 | 400 | BU2334 | F141689 | Running-in time | 28.07.2022 | 02:16:00 | 28.07.2022 | 02:34:00 | 0,3 | ||
| 12 | 400 | BU2334 | F141689 | Manufacturing time | 28.07.2022 | 02:34:00 | 28.07.2022 | 08:15:00 | 5,7 | ||
| 12 | 400 | BU2334 | F141689 | Cleaning time | 28.07.2022 | 08:15:00 | 28.07.2022 | 09:05:00 | 0,8 | ||
| 12 | 400 | BU2335 | F141689 | Set-up time | 28.07.2022 | 09:05:00 | 28.07.2022 | 10:01:00 | 0,9 | ||
| 12 | 400 | BU2335 | F141689 | Running-in time | 28.07.2022 | 10:01:00 | 28.07.2022 | 10:17:00 | 0,3 | ||
| 12 | 400 | BU2335 | F141689 | Manufacturing time | 28.07.2022 | 10:17:00 | 28.07.2022 | 10:47:00 | 0,5 | ||
| 12 | 400 | BU2335 | F141689 | Cleaning time | 28.07.2022 | 10:47:00 | 28.07.2022 | 11:21:00 | 0,6 | ||
| 12 | 400 | BU2402 | F139923 | Set-up time | 28.07.2022 | 11:21:00 | 28.07.2022 | 12:19:00 | 1,0 | ||
| 12 | 400 | BU2402 | F139923 | Running-in time | 28.07.2022 | 12:19:00 | 28.07.2022 | 12:40:00 | 0,4 | ||
| 12 | 400 | BU2402 | F139923 | Manufacturing time | 28.07.2022 | 12:40:00 | 28.07.2022 | 14:00:00 | 1,3 | ||
| 13 | -- | ------ | -1 | Machine downtime: lack of staff | 27.07.2022 | 17:03:00 | 27.07.2022 | 17:55:00 | 0,9 | ||
| 13 | -- | ------ | -1 | Waiting for setter | 27.07.2022 | 19:10:00 | 27.07.2022 | 20:20:00 | 1,2 | ||
| 13 | -- | ------ | -1 | Machine downtime: no order | 28.07.2022 | 12:05:00 | 28.07.2022 | 14:00:00 | 1,9 | ||
| 13 | 5/12,5 | BU2436 | F139307 | Set-up time | 28.07.2022 | 10:54:00 | 28.07.2022 | 12:05:00 | 1,2 | ||
| 13 | 25 | BU2382 | F122126 | Manufacturing time | 26.07.2022 | 17:35:00 | 27.07.2022 | 08:38:00 | 2,6 | 35.049,0 | 70.098,0 |
| 13 | 25 | BU2382 | F122126 | Cleaning time | 27.07.2022 | 08:38:00 | 27.07.2022 | 09:26:00 | 0,8 | ||
| 13 | 25 | BU2435 | F122094 | Set-up time | 27.07.2022 | 09:26:00 | 27.07.2022 | 10:22:00 | 0,9 | 20.049,0 | 40.098,0 |
| 13 | 25 | BU2435 | F122094 | Running-in time | 27.07.2022 | 10:22:00 | 27.07.2022 | 10:41:00 | 0,3 | ||
| 13 | 25 | BU2435 | F122094 | Manufacturing time | 27.07.2022 | 10:41:00 | 27.07.2022 | 16:41:00 | 6,0 | ||
| 13 | 25 | BU2435 | F122094 | Cleaning time | 27.07.2022 | 16:41:00 | 27.07.2022 | 17:03:00 | 0,4 | ||
| 13 | 25 | BU2497 | F140694 | Set-up time | 27.07.2022 | 17:55:00 | 27.07.2022 | 19:10:00 | 1,3 | 5.009,0 | 15.027,0 |
| 13 | 25 | BU2497 | F140694 | Set-up time | 27.07.2022 | 20:20:00 | 28.07.2022 | 03:00:00 | 6,7 | ||
| 13 | 25 | BU2497 | F140694 | Running-in time | 28.07.2022 | 03:00:00 | 28.07.2022 | 03:12:00 | 0,2 | ||
| 13 | 25 | BU2497 | F140694 | Manufacturing time | 28.07.2022 | 03:12:00 | 28.07.2022 | 06:37:00 | 3,4 | ||
| 13 | 25 | BU2497 | F140694 | Cleaning time | 28.07.2022 | 06:37:00 | 28.07.2022 | 10:54:00 | 4,3 | ||
| Machine 12 | Machine 12 | ||||||||||||||||||||||||
| Guide number | # | Part number | Parts label | Bl./Pckg. | Amount | Quantity 2 | Start order | Ende of order | Duration | FS- Size | ATP Bulk | ATP PM | Machine | Matchcode2 | Production Order Number | Material Nr | Start Date | Start Time | End Date | End Time | Duration | Quantity 1 | Quantity 2 | Reson for delay | |
| BU2290 | 22010A | F011297 | Espumisan Kautabl. 100KTbl. N3 | 10 | 1.967,0 | 19.670 | 25.07.22 17:50 | 25.07.22 21:25 | 3,58 | 60 (V136916 47x60x110) | ✔ | ✔ | 12 | IBU 400 | F139923 | 24289 | 28.07.2022 | 11:21:00 | 29.07.2022 | 06:00:00 | 18,7 | 163.413,0 | 328.826,0 | Hyperlink to Sheet of Machine 12 | |
| BU2327 | 22069B | F141689 | MIG 20FTbl | 2 | 2.248,0 | 4.496 | 26.07.22 05:40 | 26.07.22 06:40 | 1,00 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2328 | 22095A | F141689 | MIG 20FTbl | 2 | 23.700,0 | 47.400 | 26.07.22 08:10 | 26.07.22 15:05 | 6,91 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2329 | 22096A | F141689 | MIG 20FTbl | 2 | 23.442,0 | 46.884 | 26.07.22 16:35 | 26.07.22 23:25 | 6,83 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2330 | 22098A | F141689 | MIG 20FTbl | 2 | 22.448,0 | 44.896 | 27.07.22 00:55 | 27.07.22 07:27 | 6,54 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2331 | 22099A | F141689 | MIG 20FTbl | 2 | 23.368,0 | 46.736 | 27.07.22 08:57 | 27.07.22 15:46 | 6,81 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2332 | 22100A | F141689 | MIG 20FTbl | 2 | 22.927,0 | 45.854 | 27.07.22 17:16 | 27.07.22 23:57 | 6,68 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2333 | 22101A | F141689 | MIG 20FTbl | 2 | 20.498,0 | 40.996 | 28.07.22 01:27 | 28.07.22 07:26 | 5,98 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2334 | 22102A | F141689 | MIG 20FTbl | 2 | 23.516,0 | 47.032 | 28.07.22 08:56 | 28.07.22 15:47 | 6,86 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2335 | 22103A | F141689 | MIG 20FTbl | 2 | 2.853,0 | 5.706 | 28.07.22 17:17 | 28.07.22 18:17 | 1,00 | 26 (V125586 47x26x110) | ✔ | ✔ | |||||||||||||
| BU2402 | 22103B | F139923 | MIG 20 FTbl | 2 | 19.099,0 | 38.198 | 28.07.22 19:47 | 29.07.22 01:21 | 5,57 | 26 (V139926 47x26x110) | |||||||||||||||
| BU2403 | 22105A | F139923 | MIG 20 FTbl | 2 | 3.901,0 | 7.802 | 29.07.22 02:51 | 29.07.22 03:59 | 1,14 | 26 (V139926 47x26x110) | |||||||||||||||
| BU2407 | 22105B | F139922 | MIG 10FTbl | 1 | 38.862,0 | 38.862 | 29.07.22 08:59 | 29.07.22 16:19 | 7,33 | 20 (V139924 47x20x110) | |||||||||||||||
| BU2408 | 22106A | F139922 | MIG 10FTbl | 1 | 7.138,0 | 7.138 | 29.07.22 17:49 | 29.07.22 19:10 | 1,35 | 20 (V139924 47x20x110) | |||||||||||||||
| BU2409 | 22106B | F141584 | MIG-400 10FTbl | 1 | 30.000,0 | 30.000 | 29.07.22 20:40 | 30.07.22 02:20 | 5,66 | 20 (V141582 47x20x110) | |||||||||||||||
| BU2412 | 22106C | F143438 | Ibustar 10FTbl | 1 | 10.598,0 | 10.598 | 30.07.22 03:50 | 30.07.22 05:50 | 2,00 | 20 (V097228 47x20x110) | |||||||||||||||
| BU2413 | 22107A | F143438 | Ibustar 10FTbl | 1 | 9.402,0 | 9.402 | 31.07.22 23:20 | 01.08.22 01:06 | 1,77 | 20 (V097228 47x20x110) | |||||||||||||||
| Machine 12 | ||||||||
| Machine | Matchcode2 | Material Nr | Activity | Start Date | Start Time | End Date | End Time | Machine Time Booking (h) |
| 12 | -- | -1 | Repair | 27.07.2022 | 20:13:00 | 27.07.2022 | 20:22:00 | 0,2 |
