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
1DateTimeConsumedWhDelvWhUnitModeZoneNew UnitModeNew Zone
201/10/2017 07:44302222
301/10/2017 07:4512150022
401/10/2017 07:467392222
501/10/2017 07:47202222
601/10/2017 07:48132222
701/10/2017 07:49402222
801/10/2017 07:5013210022
901/10/2017 07:515380022
1001/10/2017 07:52100022Only the first 3 zeros after a
1101/10/2017 07:53130000mode change are of interest.
1201/10/2017 07:54200000
1301/10/2017 07:55100000
1401/10/2017 07:5614622323
1501/10/2017 07:5714642323
1601/10/2017 07:5814652323
1701/10/2017 07:5915660023
1801/10/2017 08:005642323
1901/10/2017 08:01262323
2001/10/2017 08:0212582121
2101/10/2017 08:0313592121
2201/10/2017 08:0413602121
2301/10/2017 08:0512580021
2401/10/2017 08:0613560001
2501/10/2017 08:0719362323
2601/10/2017 08:0828512323
2701/10/2017 08:09561461010a change from UnitMode 2 to 1 is of no concern.
2801/10/2017 08:10571461010
2901/10/2017 08:11571441010
3001/10/2017 08:12581440000a change from UnitMode 1 to 0 is of no concern.
3101/10/2017 08:13101610000
3201/10/2017 08:14102222
3301/10/2017 08:15252222
3401/10/2017 08:16400022
3501/10/2017 08:177180022
3601/10/2017 08:181100022
3701/10/2017 08:19120000
3801/10/2017 08:20102222
3901/10/2017 08:21800022
4001/10/2017 08:224272222
4101/10/2017 08:23102222
4201/10/2017 08:24132222
4301/10/2017 08:25122222
4401/10/2017 08:26500022
4501/10/2017 08:277202222
4601/10/2017 08:28172222
4701/10/2017 08:29132222
4801/10/2017 08:30420022
4901/10/2017 08:3112140022
5001/10/2017 08:321252222
5101/10/2017 08:33102222
5201/10/2017 08:34250022
5301/10/2017 08:35900022
5401/10/2017 08:361222222
5501/10/2017 08:37202222
5601/10/2017 08:38152222
5701/10/2017 08:39900022
5801/10/2017 08:405352222

<tbody>
</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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
I am not seeing the new code throw any errors. I am not certain what it is you are asking now?
 
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

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
200
etaf
etaf
SaraWitch
  • Solved
Extract individual lines from cell
Replies
6
Views
239
SaraWitch
SaraWitch
M
  • Solved
How to count certain month from a column
Replies
3
Views
108
Joe4
Joe4
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
156
XLfriend777
XLfriend777
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw

Forum statistics

Threads
1,214,895
Messages
6,122,128
Members
449,066
Latest member
Andyg666

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