Calculate COUNT of consecutive values in a column with Excel VBA-CodePudding

I have the below data in an excel spreadsheet

I want to cycle through column B and capture consecutive Paid Leaves for each employee and generate a another aggregated table like the below one:

So for each employee to calculate the days of consecutive Paid Leaves in an extra row together with a Start and an End date (Start the first day and End the last day of the consecutive Leave).

I haven't managed to think of a way to tackle that. I have minimum experience in VBA and this logic seems very complex to my knowledge so far. I would appreciate if anyone could help.

CodePudding user response：

This was getting too long for a comment so here's to help you on your way: We try not to just make things for others without OP even trying to code anything. To get into VBA, try out things with the macro reader, then read up on How to avoid using Select in Excel VBA and perhaps start off with https://www.simplilearn.com/tutorials/excel-tutorial/excel-vba so you understand a bit what everything does/is for. As for logic getting from initial to end state:

declare and set your workbook(s) and worksheets
Currentrow = 1 (or wherever you want to start from)
go from 1 to Lastrow through your B-column with for-loop (For i = Currentrow to Lastrow)
if Range("B" & i).Value2 = "Paid Leave"
Stuff i in a different variable to store your startRow (if multiple days off work)
Same but in your endrow variable (this will come in handy later)
Then use a while loop to check if the next row has Paid leave as well and adjust your endrow accordingly
In the same while loop adjust Currentrow to Currentrow 1 (for each row the next is paid leave, you need to increase your step in the for loop)
amtDays = amtDays 1 (end of while loop)
still in that If statement btw:
get your lastrow of different sheet (lastrow2) and then fill out the values with the help of your variables, i.e. Start Date column would get wbOther.wsOther.Range("D" & lastrow2 1).Value2 = wbStart.wsStart.Range("A" & startRow).Value2
amtDays = 1 (end of if)
sort on date (you can do this in your other sheet at the end)

Then if you're still stuck after trying all this, come back and we'll be happy to help :)

CodePudding user response：

I have managed to write something after Notus_Panda comment but still can't figure out how to use the While loop and how to set some variables. This is what I have written so far:

Sub paid_leave()
Dim wb As Workbook

Set wb = ActiveWorkbook
Currentrow = 2

lastrow = wb.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
amtDays = 1
For i = Currentrow To lastrow
    If wb.Sheets("Sheet1").Range("B" & i).Value2 = "Paid Leave" Then
        startRow = i
        endrow = i
        Do While Currentrow <> lastrow
            If wb.Sheets("Sheet1").Range("B" & i   1).Value2 = "Paid Leave" Then
                endrow = endrow   1
                Currentrow = Currentrow   1
                amtDays = amtDays   1
            End If
        Loop
        lastrow2 = wb.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
        wb.Sheets("Sheet2").Range("A" & lastrow2   1).Value2 = wb.Sheets("Sheet1").Range("C" & startRow).Value2
        wb.Sheets("Sheet2").Range("B" & lastrow2   1).Value2 = wb.Sheets("Sheet1").Range("B" & startRow).Value2
        wb.Sheets("Sheet2").Range("C" & lastrow2   1).Value2 = amtDays
        wb.Sheets("Sheet2").Range("D" & lastrow2   1).Value2 = wb.Sheets("Sheet1").Range("A" & startRow).Value2
        wb.Sheets("Sheet2").Range("E" & lastrow2   1).Value2 = wb.Sheets("Sheet1").Range("A" & endrow).Value2
        amtDays = 1
    End If
        Next i
End Sub

and it generates the following result which is not correct:

false result