Find row where date is between start and end date-CodePudding

First post/question so feel free to let me know if I need to add anything.

In VBA(Excel) I'm trying to find the row where my date variable falls between the start (Column A) and end dates (Column B).

The dates are contiguous, no gaps or overlaps and are all in date format (dd/mm/yyyy) therefore, I should only get one row.

The final row has an end date of 31/12/9999

StartDate	EndDate
01/04/2022	03/04/2022
04/04/2022	02/10/2022
03/10/2022	03/10/2022
04/10/2022	21/02/2023
22/02/2023	31/12/9999

If my date variable was 03/10/2022, I would get row 3. If it was 22/09/2022 I would get row 2.

In SQL I would just do WHERE date BETWEEN StartDate AND EndDate

How do I do this in VBA?

Sub getrow()

' fails on the if statement
Dim myrow As Integer
Dim myDate As Date

myDate = "01/05/2022"

If myDate >= Sheet9.Range("A:A").Value And myDate <= Sheet9.Range("B:B").Value Then

'i have hardcoded 99 but need it to show the row number where the date falls between column a and b

myrow = 99
Else
myrow = -1

End If

'putting it in a cell for now to see output
Sheet9.Range("c15").Value = myrow

End Sub

CodePudding user response：

Something like this.

Sub BetweenDates()

Dim aCell As Range, bCell As Range
Dim Start
Dim Off

    On Error GoTo ErrorHandler
    Start = CDate(InputBox("Start:"))
    Set aCell = Columns(1).Find(DateValue(Start), LookIn:=xlFormulas)
    If aCell Is Nothing Then GoTo ErrorHandler
    Off = CDate(InputBox("End:"))
    Set bCell = Columns(1).Find(DateValue(Off), LookIn:=xlFormulas)
    If bCell Is Nothing Then GoTo ErrorHandler
    Worksheets("Sheet2").Cells.ClearContents
    Rows(aCell.Row & ":" & bCell.Row).Copy Worksheets("Sheet2").Range("A1")
    End

ErrorHandler:

    Beep
    MsgBox "Not valid Date!"

End Sub

Before:

Enter '1/1/2012' and '1/10/2012'

After:

CodePudding user response：

I got it to work, there is probably a better way to do it.

Sub getrow()

Dim myrow As Integer
Dim myDate As Date
Dim rownum As Integer

myDate = #1/23/2023#

For rownum = 2 To 12

If myDate >= Cells(rownum, 1).Value And myDate <= Cells(rownum, 2).Value Then
myrow = rownum 
End If

Next rownum

MsgBox myrow

End Sub

CodePudding user response：

If the data is definitely as you've posted you could use Application.Match.

Sub getrow()
Dim myDate As Date
Dim myrow As Variant

    myDate = #1/23/2030#

    myrow = Application.Match(CDbl(myDate), Range("A2:A20"), 1)

    MsgBox myrow

End Sub