Date Lookup Return row

[hcwest3]

I am in need of vb code that takes date imputed into cell and returns the row number associated with date range.
example below Enter Date of 2/14/2019 and code would return Row number as 3 ( for 2/1/2019 thru 2/28/2019 )


[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD][/TD]
[TD]Enter Date:[/TD]
[TD]2/14/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]1/31/2019[/TD]
[TD][/TD]
[TD] Row:[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]2/28/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD]3/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/1/2019[/TD]
[TD]4/30/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

note: I have columns in column A (start date) as namedRange "StateDates"
and columns in B(End Date) as namedRange "EndDates"

any help would be greatly appreciated.

Regards,
Hank West

 

[JoeMo]

This could easily be converted to a worksheet_change event macro to run automatically whenever a user inputs a date in cell E1.

Sub GetRow()
Dim Rstart As Range, Rend As Range, D As Date,V As Variant, i As Long, Rw As Long
Set Rstart = Range("StartDates"): Set Rend = Range("EndDates")
Application.ScreenUpdating = False
Rend.Cells(1, 1).Offset(-1, 2).Resize(2, 1).Value = Application.Transpose(Array("Enter Date:", "Row:"))
If Rend.Cells(1, 1).Offset(-1, 3) = "" Or Not IsDate(Rend.Cells(1, 1).Offset(-1, 3)) Then Exit Sub
D = Rend.Cells(1, 1).Offset(-1, 3).Value
V = Range(Rstart, Rend).Value
For i = LBound(V, 1) To UBound(V, 1)
    If D >= V(i, 1) And D <= V(i, 2) Then
        Rw = i + 1
        Rend.Cells(1, 1).Offset(0, 3).Value = Rw
        Exit For
    End If
Next i
If Rw = 0 Then MsgBox "Date entered is not found in search range"
Application.ScreenUpdating = True
End Sub

 

[hcwest3]

Joe, thanks for your reply , looks like it should work fine. I will be testing tonight. Rather than a worksheet_change. I will need to convert to a function call where the date to find is passed

xRow = getrow(04/13/2019)

Thanks
Hank