Select Case with criteria for two different columns

U

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:
Code: 
ZonePrev2 = ws.Range("E" & (i - 2)).Value
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.)

ABCDEFGHIJK
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:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
The mismatch 13 is, as you thought, due to the fact that there is not enough rows above to accommodate your zoneprev2 or zoneprev3. This error will need to be captured. I would use an Select case that directs the code.

You will also have to determine how you will handle your logic when the data is at the top of your iterations. What happens if there are no values for zoneprev2 or zoneprev3?

Code: 
        Select Case i
            Case 2
            Case 3
                Zoneprev = ws.Range("E" & (i - 1)).Value
            Case 4
                Zoneprev = ws.Range("E" & (i - 1)).Value
                ZonePrev2 = ws.Range("E" & (i - 2)).Value
            Case Else
                Zoneprev = ws.Range("E" & (i - 1)).Value
                ZonePrev2 = ws.Range("E" & (i - 2)).Value
                ZonePrev3 = ws.Range("E" & (i - 3)).Value
        End Select
 
Upvote 0
Thanks RCBricker. That looks good, inserting a blank Case 2, which I'll try now.

I don't know what you mean by "You will also have to determine how you will handle your logic when the data is at the top of your iterations" - do you mean if there are no Headings? All datasets have Headings and there are no blank entries.

Will report back after some testing. Thanks again.
 
Upvote 0
Hi again. I have come up with a compromise. I will forfeit the 2nd and 3rd lines of data by setting them equal to the original data, two columns previously. The code is to be used on a calendar month of 60-sec data which is > 40,000 rows, so skipping the first 3 lines will have a negligible effect.

I'll paste the code here in case it is of use, or if anyone spots a problem with it. Many thanks for your thoughts on this.

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 three rows special case just equals UnitMode and Zone to allow 3 rows back to be checked
    ws.Range("F" & startRow).Value = ws.Range("D" & startRow).Value
    ws.Range("F" & startRow + 1).Value = ws.Range("D" & startRow + 1).Value
    ws.Range("F" & startRow + 2).Value = ws.Range("D" & startRow + 2).Value
    ws.Range("G" & startRow).Value = ws.Range("E" & startRow).Value
    ws.Range("G" & startRow + 1).Value = ws.Range("E" & startRow + 1).Value
    ws.Range("G" & startRow + 2).Value = ws.Range("E" & startRow + 2).Value


    ' Go through the UnitMode and Zone columns
    For i = startRow + 3 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
 
Upvote 0
UseLessFuel said:
Thanks RCBricker. That looks good, inserting a blank Case 2, which I'll try now.

I don't know what you mean by "You will also have to determine how you will handle your logic when the data is at the top of your iterations" - do you mean if there are no Headings? All datasets have Headings and there are no blank entries.

Will report back after some testing. Thanks again.
Click to expand...
no you have values based on information that has happened before the affected row. What do you do if there is no data, or not enough? That is what I meant.
 
Upvote 0
Okay, but there is always data. It's historical, static data with no blank entries and >40,000 rows. I should have mentioned that.
 
Upvote 0
RCBricker said:
I am not seeing the new code throw any errors. I am not certain what it is you are asking now?
Click to expand...

Thanks for the feedback. It was just in case you had spotted any glaring omissions - there was no specific question. I really am quite inexperienced with VBA - this being my best effort so far, with the help of Rlv earlier, and now yourself. I'm learning with each post and reply, so thanks for your time. Really appreciate it.
 
Upvote 0
no problem. Anytime. There are some much better people on here than me, I am still basically learning. But this site has done so much for me I try to pay it forward on here when I can.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
Seafox
  • Question Question
Glide Path Calculation
Replies
3
Views
159
RobP
R
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
197
sanrama
S
H
  • Solved
Query not pulling right info when running in VBA
2
Replies
13
Views
396
Holley
H

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top