UseLessFuel
New Member
- Joined
- Dec 22, 2012
- Messages
- 37
With reference to https://www.mrexcel.com/forum/excel-questions/1080840-vba-extract-cell-data-based-if.html
Hello. I am working on domestic heating sensor data. See a sample Worksheet with my desired results shown in column "G". The raw data is in columns "A" to "E". When column "D" = 2, column "E" can only be either 1, 2 or 3. When column "D" = 0 or 1, column "E" is always = 0.
The existing code below populates a new column "F", and places the number "2" in the current row if the same row in column "D" = 0 AND the previous row in column "D" = 2 AND the current row in Column "B" is > 0 (see cell F2 in comparison to cell D2). If column D still = 0 in the following row(s), up to a maximum of three "2"'s are placed in column "F". (See cells F8..F10 compared with D8..D10).
Similarly, I would now like a new column "G" which will equal the number in Zone column "E" unless there is a UnitMode change from 2 to 0 in column "D", again up to a maximum of three 0's. However, as stated above, the Zone column "E" can have the value 0, 1, 2 or 3, so if column "D" changes from 2 to 0 AND column "E" changes from 1 to 0 as in Row 23, I would like cell G23 to equal the previous Zone number (in column "E"), which is 1 in this case. Again if up to 3 continuous 0's occur after a 2 in column "D", I would like all three (but no more than three) corresponding row cells in column "G" to equal the previous column "E" number, which will be 1, 2 or 3.
Another way to look at it is when column "F" differs from column "D", the NewZone in column "G" will also differ from column "E", but the NewZone should equal the previous Zone number (1, 2 or 3) up to a maximum of three times.
I attempted to modify the code by introducing four new variables: Zone, Zoneprev, Zoneprev2 and Zoneprev3 but I get a Runtime error '13' type mismatch during the first run, at the line:
which may be due to the fact that there are not two rows of data above Row 2.
Again, your help would be much appreciated. (Thanks to Rlz for helping me with the code from the initial thread.)
<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]DateTime[/TD]
[TD="align: center"]ConsumedWh[/TD]
[TD="align: center"]DelvWh[/TD]
[TD="align: center"]UnitMode[/TD]
[TD="align: center"]Zone[/TD]
[TD="align: center"]New UnitMode[/TD]
[TD="align: center"]New Zone[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]01/10/2017 07:44[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]01/10/2017 07:45[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]01/10/2017 07:46[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]01/10/2017 07:47[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]01/10/2017 07:48[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]01/10/2017 07:49[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]01/10/2017 07:50[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]01/10/2017 07:51[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]01/10/2017 07:52[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]01/10/2017 07:53[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]01/10/2017 07:54[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]01/10/2017 07:55[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="align: right"]01/10/2017 07:56[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]15[/TD]
[TD="align: right"]01/10/2017 07:57[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]16[/TD]
[TD="align: right"]01/10/2017 07:58[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]17[/TD]
[TD="align: right"]01/10/2017 07:59[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 3 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]18[/TD]
[TD="align: right"]01/10/2017 08:00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]19[/TD]
[TD="align: right"]01/10/2017 08:01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]20[/TD]
[TD="align: right"]01/10/2017 08:02[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]21[/TD]
[TD="align: right"]01/10/2017 08:03[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]22[/TD]
[TD="align: right"]01/10/2017 08:04[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]23[/TD]
[TD="align: right"]01/10/2017 08:05[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]24[/TD]
[TD="align: right"]01/10/2017 08:06[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]25[/TD]
[TD="align: right"]01/10/2017 08:07[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]26[/TD]
[TD="align: right"]01/10/2017 08:08[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]27[/TD]
[TD="align: right"]01/10/2017 08:09[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]28[/TD]
[TD="align: right"]01/10/2017 08:10[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]29[/TD]
[TD="align: right"]01/10/2017 08:11[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]30[/TD]
[TD="align: right"]01/10/2017 08:12[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31[/TD]
[TD="align: right"]01/10/2017 08:13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]32[/TD]
[TD="align: right"]01/10/2017 08:14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]33[/TD]
[TD="align: right"]01/10/2017 08:15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]34[/TD]
[TD="align: right"]01/10/2017 08:16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]35[/TD]
[TD="align: right"]01/10/2017 08:17[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]36[/TD]
[TD="align: right"]01/10/2017 08:18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]37[/TD]
[TD="align: right"]01/10/2017 08:19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]38[/TD]
[TD="align: right"]01/10/2017 08:20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]39[/TD]
[TD="align: right"]01/10/2017 08:21[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]40[/TD]
[TD="align: right"]01/10/2017 08:22[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]41[/TD]
[TD="align: right"]01/10/2017 08:23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]42[/TD]
[TD="align: right"]01/10/2017 08:24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]43[/TD]
[TD="align: right"]01/10/2017 08:25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]44[/TD]
[TD="align: right"]01/10/2017 08:26[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]45[/TD]
[TD="align: right"]01/10/2017 08:27[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]46[/TD]
[TD="align: right"]01/10/2017 08:28[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]47[/TD]
[TD="align: right"]01/10/2017 08:29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]48[/TD]
[TD="align: right"]01/10/2017 08:30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]49[/TD]
[TD="align: right"]01/10/2017 08:31[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]50[/TD]
[TD="align: right"]01/10/2017 08:32[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]51[/TD]
[TD="align: right"]01/10/2017 08:33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]52[/TD]
[TD="align: right"]01/10/2017 08:34[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]53[/TD]
[TD="align: right"]01/10/2017 08:35[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]54[/TD]
[TD="align: right"]01/10/2017 08:36[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]55[/TD]
[TD="align: right"]01/10/2017 08:37[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]56[/TD]
[TD="align: right"]01/10/2017 08:38[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]57[/TD]
[TD="align: right"]01/10/2017 08:39[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]58[/TD]
[TD="align: right"]01/10/2017 08:40[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Hello. I am working on domestic heating sensor data. See a sample Worksheet with my desired results shown in column "G". The raw data is in columns "A" to "E". When column "D" = 2, column "E" can only be either 1, 2 or 3. When column "D" = 0 or 1, column "E" is always = 0.
The existing code below populates a new column "F", and places the number "2" in the current row if the same row in column "D" = 0 AND the previous row in column "D" = 2 AND the current row in Column "B" is > 0 (see cell F2 in comparison to cell D2). If column D still = 0 in the following row(s), up to a maximum of three "2"'s are placed in column "F". (See cells F8..F10 compared with D8..D10).
Similarly, I would now like a new column "G" which will equal the number in Zone column "E" unless there is a UnitMode change from 2 to 0 in column "D", again up to a maximum of three 0's. However, as stated above, the Zone column "E" can have the value 0, 1, 2 or 3, so if column "D" changes from 2 to 0 AND column "E" changes from 1 to 0 as in Row 23, I would like cell G23 to equal the previous Zone number (in column "E"), which is 1 in this case. Again if up to 3 continuous 0's occur after a 2 in column "D", I would like all three (but no more than three) corresponding row cells in column "G" to equal the previous column "E" number, which will be 1, 2 or 3.
Another way to look at it is when column "F" differs from column "D", the NewZone in column "G" will also differ from column "E", but the NewZone should equal the previous Zone number (1, 2 or 3) up to a maximum of three times.
I attempted to modify the code by introducing four new variables: Zone, Zoneprev, Zoneprev2 and Zoneprev3 but I get a Runtime error '13' type mismatch during the first run, at the line:
Code:
ZonePrev2 = ws.Range("E" & (i - 2)).Value
Again, your help would be much appreciated. (Thanks to Rlz for helping me with the code from the initial thread.)
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
Only the first 3 zeros after a | |||||||||||
mode change are of interest. | |||||||||||
a change from UnitMode 2 to 1 is of no concern. | |||||||||||
a change from UnitMode 1 to 0 is of no concern. | |||||||||||
<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]DateTime[/TD]
[TD="align: center"]ConsumedWh[/TD]
[TD="align: center"]DelvWh[/TD]
[TD="align: center"]UnitMode[/TD]
[TD="align: center"]Zone[/TD]
[TD="align: center"]New UnitMode[/TD]
[TD="align: center"]New Zone[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]01/10/2017 07:44[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]01/10/2017 07:45[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]01/10/2017 07:46[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]01/10/2017 07:47[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]01/10/2017 07:48[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]01/10/2017 07:49[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]01/10/2017 07:50[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]01/10/2017 07:51[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]01/10/2017 07:52[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]01/10/2017 07:53[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]12[/TD]
[TD="align: right"]01/10/2017 07:54[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="align: right"]01/10/2017 07:55[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="align: right"]01/10/2017 07:56[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]15[/TD]
[TD="align: right"]01/10/2017 07:57[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]16[/TD]
[TD="align: right"]01/10/2017 07:58[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]17[/TD]
[TD="align: right"]01/10/2017 07:59[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 3 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]18[/TD]
[TD="align: right"]01/10/2017 08:00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]19[/TD]
[TD="align: right"]01/10/2017 08:01[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]20[/TD]
[TD="align: right"]01/10/2017 08:02[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]21[/TD]
[TD="align: right"]01/10/2017 08:03[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]22[/TD]
[TD="align: right"]01/10/2017 08:04[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]23[/TD]
[TD="align: right"]01/10/2017 08:05[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]24[/TD]
[TD="align: right"]01/10/2017 08:06[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 1 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]25[/TD]
[TD="align: right"]01/10/2017 08:07[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]26[/TD]
[TD="align: right"]01/10/2017 08:08[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]27[/TD]
[TD="align: right"]01/10/2017 08:09[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]28[/TD]
[TD="align: right"]01/10/2017 08:10[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]29[/TD]
[TD="align: right"]01/10/2017 08:11[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]30[/TD]
[TD="align: right"]01/10/2017 08:12[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]31[/TD]
[TD="align: right"]01/10/2017 08:13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]32[/TD]
[TD="align: right"]01/10/2017 08:14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]33[/TD]
[TD="align: right"]01/10/2017 08:15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]34[/TD]
[TD="align: right"]01/10/2017 08:16[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]35[/TD]
[TD="align: right"]01/10/2017 08:17[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]36[/TD]
[TD="align: right"]01/10/2017 08:18[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]37[/TD]
[TD="align: right"]01/10/2017 08:19[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]38[/TD]
[TD="align: right"]01/10/2017 08:20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]39[/TD]
[TD="align: right"]01/10/2017 08:21[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]40[/TD]
[TD="align: right"]01/10/2017 08:22[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]41[/TD]
[TD="align: right"]01/10/2017 08:23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]42[/TD]
[TD="align: right"]01/10/2017 08:24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]43[/TD]
[TD="align: right"]01/10/2017 08:25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]44[/TD]
[TD="align: right"]01/10/2017 08:26[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]45[/TD]
[TD="align: right"]01/10/2017 08:27[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]46[/TD]
[TD="align: right"]01/10/2017 08:28[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]47[/TD]
[TD="align: right"]01/10/2017 08:29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]48[/TD]
[TD="align: right"]01/10/2017 08:30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]49[/TD]
[TD="align: right"]01/10/2017 08:31[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]50[/TD]
[TD="align: right"]01/10/2017 08:32[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]51[/TD]
[TD="align: right"]01/10/2017 08:33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]52[/TD]
[TD="align: right"]01/10/2017 08:34[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]53[/TD]
[TD="align: right"]01/10/2017 08:35[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]54[/TD]
[TD="align: right"]01/10/2017 08:36[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]55[/TD]
[TD="align: right"]01/10/2017 08:37[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]56[/TD]
[TD="align: right"]01/10/2017 08:38[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]57[/TD]
[TD="align: right"]01/10/2017 08:39[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"] 0 [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"] 2 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]58[/TD]
[TD="align: right"]01/10/2017 08:40[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Sheet1 (3)
Code:
Sub ZModeCaptWh_Heating_Data30()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim ZCnt As Long
ZCnt = 0
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
' get the last row
Dim startRow As Long, lastRow As Long
startRow = 2
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Dim i As Long, UnitMode As Long, UnitModePrev As Long, Zone As Long, Zoneprev As Long, ZonePrev2 As Long, ZonePrev3 As Long, ConsumedWh As Long
Dim FirstZeroWh As String, NewZone As String
'start row special case just equals UnitMode and Zone
ws.Range("F" & startRow).Value = ws.Range("D" & startRow).Value
ws.Range("G" & startRow).Value = ws.Range("E" & startRow).Value
' Go through the UnitMode and Zone columns
For i = startRow + 1 To lastRow
UnitMode = ws.Range("D" & i).Value
UnitModePrev = ws.Range("D" & (i - 1)).Value
ConsumedWh = ws.Range("B" & i).Value
Zone = ws.Range("E" & i).Value
Zoneprev = ws.Range("E" & (i - 1)).Value
ZonePrev2 = ws.Range("E" & (i - 2)).Value
ZonePrev3 = ws.Range("E" & (i - 3)).Value
If UnitMode = 0 And ZCnt > 0 Then
ZCnt = ZCnt + 1
Else
ZCnt = 0
End If
' Check if all IF(AND )'s are true. If true, the first Mode zero will become "2" in the new column
If UnitModePrev = 2 And UnitMode = 0 And ConsumedWh > 0 Then
ZCnt = ZCnt + 1
FirstZeroWh = "2"
NewZone = Zoneprev
Else
' The value of ZCnt is the Case number
Select Case ZCnt
'If two consecutive 0's, ZCnt will be 2 so FirstZeroWh will be 2 etc
Case 2
FirstZeroWh = "2"
NewZone = ZonePrev2
Case 3
FirstZeroWh = "2"
NewZone = ZonePrev3
'If ZCnt is above 3 which is 3 consec zero's, only the existing UnitMode will be used
Case Else
FirstZeroWh = UnitMode
NewZone = Zone
End Select
End If
' Prints the value of i to the Immediate Screen
Debug.Print i
'Write out the class to column F and new Data30 to column K
ws.Range("F" & i).Value = FirstZeroWh
ws.Range("G" & i).Value = NewZone
Next i
Application.ScreenUpdating = True 'turn it back on
Application.Calculation = xlCalculationAutomatic
End Sub
Last edited: