PDA

Vollständige Version anzeigen : Werte in Arbeitsmappe suchen und addieren


OAPlayer2306
22.07.2014, 09:27
Guten Morgen an Alle,
ich suche ein Makro, das in einer Mappe auf bestimmten Sheets Daten sucht und diese anschließend addiert.
Meine Mappe besteht aus sehr vielen Sheets unterschiedlichen Typs.

Sheet1 ist das Sheet, indem ich die Additionsergebnisse benötige:

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:bold; font-style:normal; '><colgroup><col width='28pt'><col width='100,5pt'><col width='84pt'><col width='91,5pt'><col width='91,5pt'><col width='91,5pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='6'>Arbeitsblatt mit dem Namen 'Ergebnis'</td></tr><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' style='font-size:18px; ' >Ergebnisse</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' style='font-size:18px; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' >Datum:</td><td align='middle' style='font-weight:normal; ' >14.07.2014</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' >System:</td><td align='middle' style='font-weight:normal; ' >Maschine1</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' >Datum:</td><td align='middle' >Zeit:</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>12</td><td align='middle' >Sensorart</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >M</td><td align='middle' style='font-weight:normal; ' >T</td><td align='middle' style='font-weight:normal; ' >TH</td></tr><tr><td style='background-color:#cacaca' align='middle'>13</td><td colspan='1' rowspan='2' align='middle' >Sensorgruppe</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >M [01]</td><td align='middle' style='font-weight:normal; ' >T [02]</td><td align='middle' style='font-weight:normal; ' >TH [03]</td></tr><tr><td style='background-color:#cacaca' align='middle'>14</td><td align='middle' >*</td><td align='middle' style='font-weight:normal; ' >01</td><td align='middle' style='font-weight:normal; ' >02</td><td align='middle' style='font-weight:normal; ' >03</td></tr><tr><td style='background-color:#cacaca' align='middle'>15</td><td align='middle' >statDate:</td><td align='middle' >Time:</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>16</td><td align='middle' style='font-weight:normal; ' >09.07.14</td><td align='middle' style='font-weight:normal; ' >0:00</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >58</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >1</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >33</td></tr><tr><td style='background-color:#cacaca' align='middle'>17</td><td align='middle' style='font-weight:normal; ' >09.07.14</td><td align='middle' style='font-weight:normal; ' >5:30</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >59</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >4</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >35</td></tr><tr><td style='background-color:#cacaca' align='middle'>18</td><td align='middle' style='font-weight:normal; ' >09.07.14</td><td align='middle' style='font-weight:normal; ' >12:00</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >58</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >3</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >39</td></tr><tr><td style='background-color:#cacaca' align='middle'>19</td><td align='middle' style='font-weight:normal; ' >09.07.14</td><td align='middle' style='font-weight:normal; ' >14:30</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >59</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >1</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >33</td></tr><tr><td style='background-color:#cacaca' align='middle'>20</td><td align='middle' style='font-weight:normal; ' >09.07.14</td><td align='middle' style='font-weight:normal; ' >20:30</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >58</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >1</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td></tr><tr><td style='background-color:#cacaca' align='middle'>21</td><td align='middle' style='font-weight:normal; ' >10.07.14</td><td align='middle' style='font-weight:normal; ' >0:00</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >61</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td></tr><tr><td style='background-color:#cacaca' align='middle'>22</td><td align='middle' style='font-weight:normal; ' >10.07.14</td><td align='middle' style='font-weight:normal; ' >5:30</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >56</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td></tr><tr><td style='background-color:#cacaca' align='middle'>23</td><td align='middle' style='font-weight:normal; ' >10.07.14</td><td align='middle' style='font-weight:normal; ' >12:00</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >56</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td></tr><tr><td style='background-color:#cacaca' align='middle'>24</td><td align='middle' style='font-weight:normal; ' >10.07.14</td><td align='middle' style='font-weight:normal; ' >14:30</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >59</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td></tr><tr><td style='background-color:#cacaca' align='middle'>25</td><td align='middle' style='font-weight:normal; ' >10.07.14</td><td align='middle' style='font-weight:normal; ' >20:30</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >58</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >"=…"</td></tr><tr><td style='background-color:#cacaca' align='middle'>26</td><td align='middle' style='font-weight:normal; ' >02.06.14</td><td align='middle' style='font-weight:normal; ' >0:00</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >0</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >0</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >0</td></tr><tr><td style='background-color:#cacaca' align='middle'>27</td><td align='middle' style='font-weight:normal; ' >02.06.14</td><td align='middle' style='font-weight:normal; ' >5:30</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >0</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >0</td><td align='middle' style='background-color:#FFFF00; font-weight:normal; ' >0</td></tr></table><table style='font-family:Arial; font-size:7pt'><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>
Außerdem enthält die Mappe diverse Meßprotokolle.
Hier sind 2 als Beispiel:
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='10' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:bold; font-style:normal; '><colgroup><col width='28pt'><col width='62,25pt'><col width='65,25pt'><col width='29,25pt'><col width='29,25pt'><col width='42pt'><col width='53,25pt'><col width='29,25pt'><col width='29,25pt'><col width='29,25pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='10'>Arbeitsblatt mit dem Namen '20140709_Maschine1'</td></tr><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td><td align='middle'>F</td><td align='middle'>G</td><td align='middle'>H</td><td align='middle'>I</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' >Meßprotokoll</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >Datum:</td><td align='middle' >09.07.2014</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' >*</td><td align='middle' >Sensorgruppe</td><td align='middle' >00:00</td><td align='middle' >05:30</td><td align='middle' >12:00</td><td align='middle' >12:30</td><td align='middle' >14:30</td><td align='middle' >20:30</td><td align='middle' >21:00</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' style='font-weight:normal; ' >Sensor M1</td><td align='middle' style='font-weight:normal; ' >01</td><td align='middle' style='font-weight:normal; ' >55</td><td align='middle' style='font-weight:normal; ' >56</td><td align='middle' style='font-weight:normal; ' >55</td><td align='middle' style='font-weight:normal; ' >56</td><td align='middle' style='font-weight:normal; ' >56</td><td align='middle' style='font-weight:normal; ' >53</td><td align='middle' style='font-weight:normal; ' >53</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' style='font-weight:normal; ' >Sensor M2</td><td align='middle' style='font-weight:normal; ' >01</td><td align='middle' style='font-weight:normal; ' >2</td><td align='middle' style='font-weight:normal; ' >2</td><td align='middle' style='font-weight:normal; ' >2</td><td align='middle' style='font-weight:normal; ' >2</td><td align='middle' style='font-weight:normal; ' >2</td><td align='middle' style='font-weight:normal; ' >4</td><td align='middle' style='font-weight:normal; ' >6</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' style='font-weight:normal; ' >Sensor M3</td><td align='middle' style='font-weight:normal; ' >01</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' style='font-weight:normal; ' >Sensor T1</td><td align='middle' style='font-weight:normal; ' >02</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' style='font-weight:normal; ' >Sensor T2</td><td align='middle' style='font-weight:normal; ' >02</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >3</td><td align='middle' style='font-weight:normal; ' >2</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >0</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' style='font-weight:normal; ' >Sensor T3</td><td align='middle' style='font-weight:normal; ' >03</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >2</td><td align='middle' style='font-weight:normal; ' >6</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >0</td><td align='middle' style='font-weight:normal; ' >0</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' style='font-weight:normal; ' >Sensor T4</td><td align='middle' style='font-weight:normal; ' >03</td><td align='middle' style='font-weight:normal; ' >33</td><td align='middle' style='font-weight:normal; ' >33</td><td align='middle' style='font-weight:normal; ' >33</td><td align='middle' style='font-weight:normal; ' >33</td><td align='middle' style='font-weight:normal; ' >33</td><td align='middle' style='font-weight:normal; ' >33</td><td align='middle' style='font-weight:normal; ' >28</td></tr><tr><td style='background-color:#cacaca' align='middle'>10</td><td align='middle' style='font-weight:normal; ' >Sensor TH14</td><td align='middle' style='font-weight:normal; ' >04</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td><td align='middle' style='font-weight:normal; ' >1</td></tr><tr><td style='background-color:#cacaca' align='middle'>11</td><td align='middle' style='font-weight:normal; ' >Sensor TH15</td><td align='middle' style='font-weight:normal; ' >04</td><td align='middle' style='font-weight:normal; ' >14</td><td align='middle' style='font-weight:normal; ' >14</td><td align='middle' style='font-weight:normal; ' >14</td><td align='middle' style='font-weight:normal; ' >14</td><td align='middle' style='font-weight:normal; ' >14</td><td align='middle' style='font-weight:normal; ' >14</td><td align='middle' style='font-weight:normal; ' >14</td></tr></table><table style='font-family:Arial; font-size:7pt'><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>und
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='12' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='62,25pt'><col width='76,5pt'><col width='29,25pt'><col width='29,25pt'><col width='42pt'><col width='53,25pt'><col width='29,25pt'><col width='29,25pt'><col width='29,25pt'><col width='29,25pt'><col width='29,25pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='12'>Arbeitsblatt mit dem Namen '20140710_Maschine1'</td></tr><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td><td align='middle'>F</td><td align='middle'>G</td><td align='middle'>H</td><td align='middle'>I</td><td align='middle'>J</td><td align='middle'>K</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' >Meßprotokoll</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >Datum:</td><td align='middle' >10.07.2014</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' >*</td><td align='middle' >Sensorgruppe</td><td align='middle' >00:00</td><td align='middle' >00:30</td><td align='middle' >05:30</td><td align='middle' >06:00</td><td align='middle' >06:30</td><td align='middle' >12:00</td><td align='middle' >14:30</td><td align='middle' >15:00</td><td align='middle' >20:30</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td><td align='middle' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' >Sensor M1</td><td align='middle' >01</td><td align='middle' >58</td><td align='middle' >58</td><td align='middle' >53</td><td align='middle' >58</td><td align='middle' >53</td><td align='middle' >58</td><td align='middle' >53</td><td align='middle' >58</td><td align='middle' >53</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' >Sensor M2</td><td align='middle' >01</td><td align='middle' >2</td><td align='middle' >2</td><td align='middle' >2</td><td align='middle' >2</td><td align='middle' >2</td><td align='middle' >2</td><td align='middle' >2</td><td align='middle' >2</td><td align='middle' >4</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' >Sensor M3</td><td align='middle' >01</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' >Sensor TH14</td><td align='middle' >04</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' >Sensor TH15</td><td align='middle' >04</td><td align='middle' >21</td><td align='middle' >21</td><td align='middle' >21</td><td align='middle' >21</td><td align='middle' >21</td><td align='middle' >21</td><td align='middle' >21</td><td align='middle' >21</td><td align='middle' >21</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' >Sensor TH16</td><td align='middle' >04</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td><td align='middle' >1</td></tr></table><table style='font-family:Arial; font-size:7pt'><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>

Im gelb markierten Bereiche benötige ich die entsprechenden Summen aus den Protokollen.
Am Beispiel für C16 sind es folgende Schritte:
1. Auf allen Protokoll-Sheets nach dem Datum 09.07.14 suchen. Das Datum befindet sich auf allen Protokoll-Sheets immer in F1.
2. Nun in den Sheets mit dem richtigen Datum nach der entsprechenden Uhrzeit 0:00 suchen (Zeile2).
3. Jetzt werden alle Werte, die zur Sensorgruppe 01 (Spalte B der Protokoll-Sheets) addiert und in C16 eingetragen (55+2+1=58). Die Sensorgruppe ist abhängig von Zeile 14 auf dem Sheet Ergebnisse.

Leider ist das Datum nicht eindeutig, es kann mehrmals vorkommen. Nur die Kombination Datum + Uhrzeit ist eindeutig, sie kommt in der Mappe nur einmal vor.

Der Name der Arbeitsmappe und der Protokoll-Sheets ändert sich oft.

Wer kann mir weiterhelfen?

Vielen Dank,
OAPlayer2306
:eek:

xlph
22.07.2014, 13:08
Hallo,

siehe Datei.

(Public Sub AuswertungMessprotokolle_xlph())

OAPlayer2306
22.07.2014, 14:52
Hi xlph,
dein Makro funktioniert gut.
Nur bei der Anpassung an meine Dateien hapert es zur Zeit ein wenig.
Ich habe einen Laufzeitfehler 9 in Zeile 38 (Datum = ProtokollDaten(1, 6))
Da muß ich noch forschen, habe aber im Augenblick keine Zeit.

Ich melde mich morgen nochmal,
vielen Dank,
OAPlayer2306

OAPlayer2306
23.07.2014, 14:12
Hallo xlph,
ich habe jetzt rausgefunden, warum es zu einem Laufzeitfehler kommt.
In meinen Protokollen gibt es eines, indem es nur 2 Uhrzeiten gibt.

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='9' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='9'>Arbeitsblatt mit dem Namen 'Tabelle4'</td></tr><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td><td align='middle'>F</td><td align='middle'>G</td><td align='middle'>H</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' style='font-size:7px; ' >Meßprotokoll</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >Datum:</td><td align='middle' style='font-size:7px; ' >02.06.2014</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >Sensorgruppe</td><td align='middle' style='font-size:7px; ' >00:00</td><td align='middle' style='font-size:7px; ' >00:30</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='middle' style='font-size:7px; ' >Sensor M1</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >58</td><td align='middle' style='font-size:7px; ' >58</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='middle' style='font-size:7px; ' >Sensor M2</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >2</td><td align='middle' style='font-size:7px; ' >2</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>6</td><td align='middle' style='font-size:7px; ' >Sensor M3</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>7</td><td align='middle' style='font-size:7px; ' >Sensor TH14</td><td align='middle' style='font-size:7px; ' >2</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>8</td><td align='middle' style='font-size:7px; ' >Sensor TH15</td><td align='middle' style='font-size:7px; ' >3</td><td align='middle' style='font-size:7px; ' >21</td><td align='middle' style='font-size:7px; ' >21</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>9</td><td align='middle' style='font-size:7px; ' >Sensor TH16</td><td align='middle' style='font-size:7px; ' >4</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >1</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td><td align='middle' style='font-size:7px; ' >*</td></tr></table><table style='font-family:Arial; font-size:7pt'><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>

Wenn diese Tabelle in der Mappe ist, kommt es zu dem Fehler.

Wie kann man das beheben?

Gruß,
OAPlayer2306

xlph
23.07.2014, 15:18
Hallo,

hier der korrigierte Code:

Public Sub AuswertungMessprotokolle_xlph()
Dim Blatt As Worksheet

Dim ProtokollDaten As Variant
Dim IndexPD As Long
Dim IndexPD2 As Long

Dim Datum As Date
Dim Zeit As Date
Dim SensorGruppe As Long

Dim ID As String

Dim oSumme As Object

Dim ErgebnisDaten As Variant
Dim IndexED As Long
Dim IndexED2 As Long

Set oSumme = CreateObject("scripting.dictionary")

For Each Blatt In ThisWorkbook.Worksheets
Select Case Blatt.Name
Case "Ergebnis" ', "Tabelle2", "Tabelle3" ' => keine Protokoll-Blätter: hier auflisten
Case Else

' Protokoll-Daten einlesen und summieren

With Blatt
ProtokollDaten = .Range("A1", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, _
.Cells(2, .Columns.Count).End(xlToLeft).Column))

For IndexPD = LBound(ProtokollDaten, 1) + 2 To UBound(ProtokollDaten, 1)
For IndexPD2 = LBound(ProtokollDaten, 2) + 2 To UBound(ProtokollDaten, 2)

Datum = ProtokollDaten(1, 6)
If IsNumeric(ProtokollDaten(2, IndexPD2)) Then ' << Korrektur
Zeit = ProtokollDaten(2, IndexPD2)
SensorGruppe = Val(ProtokollDaten(IndexPD, 2))

ID = Format(Datum, "dd.mm.yyyy") & "#" & _
Format(Zeit, "hh:mm") & "#" & _
Format(SensorGruppe, "00")

oSumme(ID) = oSumme(ID) + Val(ProtokollDaten(IndexPD, IndexPD2))
End If
Next
Next

End With

End Select
Next


' Ergebnis ausgeben

If oSumme.Count > 0 Then

Set Blatt = Tabelle1

With Blatt
ErgebnisDaten = .Range("A13", .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, _
.Cells(14, .Columns.Count).End(xlToLeft).Column))

For IndexED = LBound(ErgebnisDaten, 1) + 3 To UBound(ErgebnisDaten, 1)
For IndexED2 = LBound(ErgebnisDaten, 2) + 2 To UBound(ErgebnisDaten, 2)

Datum = ErgebnisDaten(IndexED, 1)
Zeit = ErgebnisDaten(IndexED, 2)
SensorGruppe = Val(ErgebnisDaten(2, IndexED2))

ID = Format(Datum, "dd.mm.yyyy") & "#" & _
Format(Zeit, "hh:mm") & "#" & _
Format(SensorGruppe, "00")

If oSumme.Exists(ID) Then
ErgebnisDaten(IndexED, IndexED2) = oSumme(ID)
End If

Next
Next

.Range("A13").Resize(UBound(ErgebnisDaten, 1), UBound(ErgebnisDaten, 2)).Value = ErgebnisDaten

End With

End If


Set Blatt = Nothing
Set oSumme = Nothing

End Sub

OAPlayer2306
23.07.2014, 15:32
Hi xlph,
die Änderung hat leider nicht geholfen.
Nur wenn in F2 auf den Protokollseiten eine Zeit eingetragen ist, läuft das Makro ohne Probleme.
Ist das Feld leer, kommt es zum Fehler.

Gruß,
OAPlayer2306

xlph
23.07.2014, 15:47
Hallo,
Nur wenn in F2 auf den Protokollseiten eine Zeit eingetragen ist, läuft das Makro ohne Probleme.

Das ist ja auch dein Sinn des Makros.

OAPlayer2306
23.07.2014, 15:59
Hi,
es wäre aber auch sinnvoll, wenn das Makro bei Protokollen funktioniert, in denen nur zwei mal am Tag ein Wert aufgenommen wird.
D.h. z.B. das in C2 und D2 eine Zeit steht, in F2 aber nichts.

Gruß,
OAPlayer2306

xlph
23.07.2014, 16:16
Na dann so:


Option Explicit

Public Sub AuswertungMessprotokolle_xlph()
Dim Blatt As Worksheet

Dim ProtokollDaten As Variant
Dim IndexPD As Long
Dim IndexPD2 As Long

Dim Datum As Date
Dim Zeit As Date
Dim SensorGruppe As Long

Dim ID As String

Dim oSumme As Object

Dim ErgebnisDaten As Variant
Dim IndexED As Long
Dim IndexED2 As Long

Set oSumme = CreateObject("scripting.dictionary")

For Each Blatt In ThisWorkbook.Worksheets
Select Case Blatt.Name
Case "Ergebnis" ', "Tabelle2", "Tabelle3" ' => keine Protokoll-Blätter: hier auflisten
Case Else

' Protokoll-Daten einlesen und summieren

With Blatt
ProtokollDaten = BenutzterBereich(.Range("A1")).Value

For IndexPD = LBound(ProtokollDaten, 1) + 2 To UBound(ProtokollDaten, 1)
For IndexPD2 = LBound(ProtokollDaten, 2) + 2 To UBound(ProtokollDaten, 2)

Datum = ProtokollDaten(1, 6)
If Not IsEmpty(ProtokollDaten(2, IndexPD2)) Then
If IsNumeric(ProtokollDaten(2, IndexPD2)) Then
Zeit = ProtokollDaten(2, IndexPD2)
SensorGruppe = Val(ProtokollDaten(IndexPD, 2))

ID = Format(Datum, "dd.mm.yyyy") & "#" & _
Format(Zeit, "hh:mm") & "#" & _
Format(SensorGruppe, "00")

oSumme(ID) = oSumme(ID) + Val(ProtokollDaten(IndexPD, IndexPD2))
End If
End If
Next
Next

End With

End Select
Next


' Ergebnis ausgeben

If oSumme.Count > 0 Then

Set Blatt = Tabelle1

With Blatt
ErgebnisDaten = BenutzterBereich(.Range("A13")).Value

For IndexED = LBound(ErgebnisDaten, 1) + 3 To UBound(ErgebnisDaten, 1)
For IndexED2 = LBound(ErgebnisDaten, 2) + 2 To UBound(ErgebnisDaten, 2)

Datum = ErgebnisDaten(IndexED, 1)
Zeit = ErgebnisDaten(IndexED, 2)
SensorGruppe = Val(ErgebnisDaten(2, IndexED2))

ID = Format(Datum, "dd.mm.yyyy") & "#" & _
Format(Zeit, "hh:mm") & "#" & _
Format(SensorGruppe, "00")

If oSumme.Exists(ID) Then
ErgebnisDaten(IndexED, IndexED2) = oSumme(ID)
End If

Next
Next

.Range("A13").Resize(UBound(ErgebnisDaten, 1), UBound(ErgebnisDaten, 2)).Value = ErgebnisDaten

End With

End If


Set Blatt = Nothing
Set oSumme = Nothing

End Sub

Private Function BenutzterBereich(StartZelle As Range) As Range
With StartZelle.Worksheet
Set BenutzterBereich = .Range(StartZelle, .Cells( _
.Cells.Find("*", , , , xlByRows, xlPrevious).Row, _
.Cells.Find("*", , , , xlByColumns, xlPrevious).Column))
End With
End Function

OAPlayer2306
23.07.2014, 16:22
Hi xlph,
so ist perfekt.

Vielen, vielen Dank,
OAPlayer2306