To find the people present on particular range of days

Question

The data is provided in excel sheet and input should be asked in a input box or in other cell in the excel. Now input should be taken then check if any person is present on those days and should reflect who is present in the output.

Data:

Input:

Output should be:

I wrote a code but unable to find where i have gone wrong. Please help me out in rectifying the code or create a new code

Sub Sai_Aircraft_log_entry1()
Dim p, e1, e2 As Date

Worksheets("Test2").Select
e1 = Cells(5, 11).Value
e2 = Cells(6, 11).Value

Sheets("Output2").Cells.Clear
Worksheets("Output2").Select
Range("A2").Select


Dim n, m, k, s As Integer
    Worksheets("Test2").Select

    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    n = Range(Selection, Selection.End(xlDown)).Count
    
Dim i, o As Date
Dim x As Integer
Dim A As String
    
    For p = e1 To e2
        Worksheets("Output2").Select
        ActiveCell.Select
        ActiveCell.Value = p
        ActiveCell.Offset(0, 1).Select
    
        For x = 2 To n
           Worksheets("Test2").Select
        'bring below line up
            i = Cells(x, 5).Value
            o = Cells(x, 6).Value
            A = Cells(x, 3).Value

            If i =< p and o => p Then
                '
                Worksheets("Output2").Select
                ActiveCell.Value = A
                ActiveCell.Offset(0, 1).Select
             End If
    Next x
    Worksheets("Output2").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    m = Range(Selection, Selection.End(xlToLeft)).Count
    k = m * -1
    s = k + 2
    'MsgBox k
    ActiveCell.Offset(1, 0).Select
    'MsgBox k
    ActiveCell.Offset(0, s).Select
    Next p
End Sub

You might benefit from reading [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613) and [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Please tell which errors you got or where you got stuck. Note that *"does not work"* is not useful as error description. — Pᴇʜ, Jul 08 '21 at 08:58

score 1 · Answer 1 · answered Jul 08 '21 at 13:27

Not sure what the problem is as your code seem to work, but it would be easier to understand by not using ActiveCell.Select.

Option Explicit
Sub Sai_Aircraft_log_entry2()

    Dim ws As Worksheet, wsOut As Worksheet
    Dim dtStart As Date, dtEnd As Date, dt As Date
    Dim dtIn As Date, dtOut As Date
    Dim iLastRow As Long, rOut As Long, r As Long, c As Long
    
    Set wsOut = Sheets("Output2")
    wsOut.Cells.Clear
    
    Set ws = Sheets("Test2")
    With ws
        dtStart = .Range("K5")
        dtEnd = .Range("K6")
        iLastRow = .Cells(Rows.Count, "C").End(xlUp).Row
    End With
      
    rOut = 2
    For dt = dtStart To dtEnd
        wsOut.Cells(rOut, 1) = dt
        ' scan in/out sheet
        c = 1
        For r = 2 To iLastRow
            dtIn = ws.Cells(r, "E")
            dtOut = ws.Cells(r, "F")
            If dt >= dtIn And dt <= dtOut Then
                c = c + 1
                wsOut.Cells(rOut, c) = ws.Cells(r, "C")
            End If
        Next
        rOut = rOut + 1
    Next

    MsgBox "Done"
End Sub

To find the people present on particular range of days

1 Answers1