Excel VBA translation date into another format

Question

Really new to the Excel VBA. Been working on one task and tried to piece all different elements into one working macro.

Here is my goal

as you can see on the picture, there is a list of ID and names who reported their leave during the month.

I would like to translate into below format start date/ end date /hours taken

1 Tried the code to capture start date, but failed to resume the loop to capture the end date.

Sub FindMatchingValue()
    Dim i As Integer, intValueToFind As Integer
    intValueToFind = 8
    For i = 1 To 500    ' Revise the 500 to include all of your values
        If Cells(2, i).Value = intValueToFind Then
            MsgBox ("Found value on row " & i)
            Cells(2, 35).Value = Cells(1, i) 'copy the start date to same row column 35
            Exit Sub
        End If
    Next i

    ' This MsgBox will only show if the loop completes with no success
    MsgBox ("Value not found in the range!")
End Sub

2 End date would be the last day for employee who took leave in consecutive days.

Really appreciate help from our community.

enter image description here

Will not resume because there is ExitSub. Also CaptureEndDate not shared with us. Why is CONSECUTIVE days in the requirement? Also, start date could be 4hrs (not 8 as intValueToFind) of the afternoon followed by a whole day. Multiple leaves for one person not shown in the data, nor handled in code. It is hard to imagine a sheet with 250 columns-- one for each business day of the year, then times 2 for two years. Design of the database needs work. — donPablo
@donPablo Hi thank for pointing it out for me. As I want to make the leave request into small block rather than one day one entry which I believe would be a little messy. I understand hours might vary and only used 8 as an test, was thinking put down as >=0. — excelabc19
@PaichengWu Hi Paicheng thanks for your reply and I updated the image now, Kindly have a look. — excelabc19

Dhirendra Kumar Dhirendra Kumar · Accepted Answer · 2019-09-26T08:10:39

Following code will return you the first set of consecutive leave for first ID (Row 2) with start date, end date and hours taken:

Sub FindMatchingValue()
    Dim i As Integer, intValueToFind As Integer, Found As Boolean, HoursTaken As Single
    intValueToFind = 8
    For i = 1 To 34    'Considering 34 is the max date column
        If Found Then
            If Cells(2, i).Value = "" Then
                MsgBox ("Last consecutive column " & i - 1)
                Cells(2, 36).Value = Cells(1, i - 1) 'copy the end date to same row column 36
                Cells(2, 37).Value = HoursTaken 'Hours taken to same row column 37
                Found = False
                Exit Sub 'Skip after first set of leave
            Else
                HoursTaken = HoursTaken + Cells(2, i)
            End If
        ElseIf Cells(2, i).Value = intValueToFind Then
            MsgBox ("Found value on column " & i)
            Cells(2, 35).Value = Cells(1, i) 'copy the start date to same row column 35
            Found = True
            HoursTaken = Cells(2, i)
        End If
    Next i

    'This MsgBox will only show if the loop completes with no success
    MsgBox ("Value not found in the range!")
End Sub

You have to think more on how will you capture the next sets of leave for the same person and run it for entire set of data row. Hope this will be help in solving your problem.

Excel VBA translation date into another format

1 Answers