VBA to extract cell data based on If ... And ... And ...

[UseLessFuel]

Hello. I am dealing with large variable-length time-series datasets of domestic heating system sensor data. I need to extract specific cells data based on the content of three cells. Only two columns of data (L and N) are of interest in the attached HTML, although the raw data extends from columns A to U. I have added column W to show what I would like final result to appear in column N.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	DateTime	Outdoor	Set room	Room	Set room (Zone2)	Room (Zone2)	Set flow	Flow	Return	Set tank	Tank	ConsumedWh	DeliveredWh	UnitMode	Defr	Data 30		Set - Z1	Set - Z2	Set - Flow	Flow - return		UnitMode		Total Mode 0 Consumed Wh		First Mode 0 after Mode 2 (Wh con)
2	01/10/2017 07:27	13	20	20.5	18	19.5	27	27	26.5	50	47	1	19	2	0	2		-0.5	-1.5	0	0.5		2		90	1st zero:	80	89%
3	01/10/2017 07:28	14	20	20.5	18	19.5	27	26.5	26.5	50	47	1	3	2	0	2		-0.5	-1.5	0.5	0		2		NEED LAST	2nd zero:	5	6%
4	01/10/2017 07:29	14	20	20.5	18	19.5	27	27	26.5	50	46.5	2	2	2	0	2		-0.5	-1.5	0	0.5		2		ROW FOR	3rd zero:	1	1%
5	01/10/2017 07:30	13	20	20.5	18	19.5	27	28.5	27	50	47	12	14	0	0	0		-0.5	-1.5	-1.5	1.5		3		ABOVE!		Above uses
6	01/10/2017 07:31	13	20	20.5	18	19.5	27	27	27	50	46.5	10	34	2	0	2		-0.5	-1.5	0	0		2				Sum (Col:Col) only
7	01/10/2017 07:32	13	20	20.5	18	19.5	27	27	27	50	46.5	1	18	2	0	2		-0.5	-1.5	0	0		2		0
8	01/10/2017 07:33	14	20	20.5	18	19.5	27	26.5	26.5	50	46.5	2	3	2	0	2		-0.5	-1.5	0.5	0		2
9	01/10/2017 07:34	13	20	20.5	18	19.5	27	27	26.5	50	46.5	1	2	2	0	2		-0.5	-1.5	0	0.5		2
10	01/10/2017 07:35	13	20	20.5	18	19.5	27	28.5	27	50	46.5	13	14	0	0	0		-0.5	-1.5	-1.5	1.5		3
11	01/10/2017 07:36	13	20	20.5	18	19.5	27	27	27	50	46.5	9	37	2	0	2		-0.5	-1.5	0	0		2
12	01/10/2017 07:37	13	20	20.5	18	19.5	27	27	27	50	46.5	1	11	2	0	2		-0.5	-1.5	0	0		2
13	01/10/2017 07:38	14	20	20.5	18	19.5	27	26.5	26.5	50	46.5	1	4	2	0	2		-0.5	-1.5	0.5	0		2
14	01/10/2017 07:39	13	20	20.5	18	19.5	27	27	26.5	50	46.5	3	2	2	0	2		-0.5	-1.5	0	0.5		2
15	01/10/2017 07:40	13	20	20.5	18	19.5	27	29	27	50	46.5	12	14	0	0	0		-0.5	-1.5	-2	2		3
16	01/10/2017 07:41	13	20	20.5	18	19.5	27	27	27	50	46.5	8	38	2	0	2		-0.5	-1.5	0	0		2
17	01/10/2017 07:42	13	20	20.5	18	19.5	27	27	27	50	46.5	1	4	2	0	2		-0.5	-1.5	0	0		2
18	01/10/2017 07:43	14	20	20.5	18	19.5	27	26.5	26.5	50	46.5	2	3	2	0	2		-0.5	-1.5	0.5	0		2
19	01/10/2017 07:44	14	20	20.5	18	19.5	27	27.5	26.5	50	47	3	0	2	0	2		-0.5	-1.5	-0.5	1		2
20	01/10/2017 07:45	13	20	20.5	18	19.5	27	29	27	50	46.5	12	15	0	0	0		-0.5	-1.5	-2	2		3
21	01/10/2017 07:46	13	20	20.5	18	19.5	27	27	27	50	46.5	7	39	2	0	2		-0.5	-1.5	0	0		2
22	01/10/2017 07:47	14	20	20.5	18	19.5	27	27	27	50	46.5	2	0	2	0	2		-0.5	-1.5	0	0		2
23	01/10/2017 07:48	14	20	20.5	18	19.5	27	26.5	26.5	50	46.5	1	3	2	0	2		-0.5	-1.5	0.5	0		2
24	01/10/2017 07:49	14	20	20.5	18	19.5	27	27.5	26.5	50	46.5	4	0	2	0	2		-0.5	-1.5	-0.5	1		2
25	01/10/2017 07:50	14	20	20.5	18	19.5	26	28.5	27	50	46.5	13	21	0	0	0		-0.5	-1.5	-2.5	1.5		3
26	01/10/2017 07:51	14	20	20.5	18	19.5	26	26.5	27	50	46.5	5	38	0	0	0		-0.5	-1.5	-0.5	-0.5		4
27	01/10/2017 07:52	14	20	20.5	18	19.5	26	26.5	26.5	50	46.5	1	0	0	0	0		-0.5	-1.5	-0.5	0		5
28	01/10/2017 07:53	15	20	20.5	18	19.5	26	26.5	26.5	50	46.5	1	3	0	0	0		-0.5	-1.5	-0.5	0		0
29	01/10/2017 07:54	15	20	20.5	18	19.5	26	26.5	26.5	50	46.5	2	0	0	0	0		-0.5	-1.5	-0.5	0		0
30	01/10/2017 07:55	15	20	20.5	18	19.5	26	26	26	50	46.5	1	0	0	0	0		-0.5	-1.5	0	0		0
31	01/10/2017 07:56	15	20	20.5	18	19.5	26	26	26	50	46.5	1	0	2	0	2		-0.5	-1.5	0	0		2
32	01/10/2017 07:57	14	20	20.5	18	19.5	26	27	26	50	46.5	3	0	2	0	2		-0.5	-1.5	-1	1		2
33	01/10/2017 07:58	14	20	20.5	18	19.5	26	27.5	26.5	50	46.5	12	15	0	0	0		-0.5	-1.5	-1.5	1		3
34	01/10/2017 07:59	14	20	20.5	18	19.5	26	26	26.5	50	46.5	5	30	2	0	2		-0.5	-1.5	0	-0.5		2
35	01/10/2017 08:00	14	20	20.5	18	19.5	26	26	26	50	46.5	1	0	2	0	2		-0.5	-1.5	0	0		2
36	01/10/2017 08:00	14	20	20.5	18	19.5	26	26	26	50	46.5	1	5	2	0	2		-0.5	-1.5	0	0		2
37	01/10/2017 08:01	14	20	20.5	18	19.5	26	27.5	26	50	46.5	6	0	0	0	0		-0.5	-1.5	-1.5	1.5		3
38	01/10/2017 08:02	14	20	20.5	18	19.5	26	26.5	26.5	50	46.5	11	33	2	0	2		-0.5	-1.5	-0.5	0		2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1 (2)​

Worksheet Formulas Cell Formula Y2 =SUMIF(N2:N38,"=0",L2:L38) AA2 =SUMIF(W:W,"=3",L:L) AA3 =SUMIF(W:W,"=4",L:L) AA4 =SUMIF(W:W,"=5",L:L) Y7 =SUMIFS(L:L,W:W,"=0",W:W,"=3",W:W,"=4",W:W,"=5") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

(Please ignore the Y7 formula above. Still getting used to the HTMLMaker!)

I will try to explain:
In column N, if a UnitMode = 2 is immediately followed by a UnitMode =0 AND if (in Column L) the ConsumedWh is >0, I wish to change the UnitMode = 0 to the number 3, otherwise leave the UnitMode as is. For the UnitMode in cell N2, an expression for this would be:

=IF(AND(N1=2,N2=0,L2>0),place 3 in N2,N2) which, in this case, would result in the UnitMode for cell N2 remaining as 2.

If a second UnitMode = 0, follows a UnitMode =2, AND the ConsumedWh>0 (as in Row 26) I wish to change the UnitMode = 0 to the number 4. Finally, if a third consecutive UnitMode = 0 follows a UnitMode =2, AND the ConsumedWh>0, I wish to change the UnitMode = 0 to the number 5 (as in Row 27). See column W for what I would like to see in Column N (without formatting).

I would also like to add some summary results, shown in the range Y1:AB4.
The equation in Y1 is: =SUMIF(W:W,"=0",L:L)+AA2+AA3+AA4
In AA2, the equation is SUMIF(W:W,"=3",L:L)
In AA3, the equation is=SUMIF(W:W,"=4",L:L)
In AA4, the equation is=SUMIF(W:W,"=5",L:L)


My attempt at coding for the first Mode = 0 following a Mode = 2, is shown below, where I had tried to create a new Column called FirstZeroWh to store the results in.

Sub ZeroModeCaptureWh() 
' get the last row
Dim startRow As Long, lastRow As Long
startRow = 2
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row


Dim i As Long, UnitMode As Long, UnitModeNext As Long, ConsumedWh As Long
Dim FirstZeroWh As String


' Go through the UnitMode column
For i = startRow To lastRow
UnitMode = Sheet1.Range("N" & i).Value
ConsumedWh = Sheet1.Range("L" & i).Value
UnitModeNext = Sheet1.Range("N" & (i + 1)).Value


' Check UnitMode and classify accordingly
If UnitMode = 2 And UnitModeNext = 0 And ConsumedWh > 0 Then
FirstZeroWh = "7"
Else
' For all other cases
FirstZeroWh = UnitMode
End If


' Write out the class to column W
Sheet1.Range("W" & i).Value = FirstZeroWh
Next


End Sub

I was doing okay until I added:
[ConsumedWh = Sheet1.Range("L" & i).Value
UnitModeNext = Sheet1.Range("N" & (i + 1)).Value]

which was a bit of a guess!

Any help would be very much appreciated.

 

[rlv01]

This only addresses the data extraction portion of your question and not the summary formulas at the bottom.

Sub ZeroModeCaptureWh()
    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, ConsumedWhPrev As Long
    Dim FirstZeroWh As String

    WS.Range("W" & startRow).Value = WS.Range("N" & startRow).Value    'start row special case

    ' Go through the UnitMode column
    For i = startRow + 1 To lastRow
        UnitMode = WS.Range("N" & i).Value
        UnitModePrev = WS.Range("N" & (i - 1)).Value
        ConsumedWhPrev = WS.Range("L" & (i - 1)).Value

        If UnitMode = 0 And ZCnt > 0 Then
            ZCnt = ZCnt + 1
        Else
            ZCnt = 0
        End If

        ' Check UnitModePrev and classify accordingly
        If UnitModePrev = 2 And UnitMode = 0 And ConsumedWhPrev > 0 Then
            ZCnt = ZCnt + 1
            FirstZeroWh = "3"
        Else
            ' For all other cases
            Select Case ZCnt
            Case 2
                FirstZeroWh = "4"
            Case 3
                FirstZeroWh = "5"
            Case Else
                FirstZeroWh = UnitMode
            End Select
        End If

        ' Write out the class to column W
        Debug.Print i
        WS.Range("W" & i).Value = FirstZeroWh
    Next i
End Sub

 

[UseLessFuel]

rlv01, that's great. Thanks for spending the time on this. I made one minor change, replacing the variable ConsumedWhPrev with ConsumedWh and the following code (so that the same row as UnitMode is checked, as opposed to the previous row):

ConsumedWh = WS.Range("L" & i).Value

It works perfectly.