• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

Find the row for a input

How to write a VBA such that when a user input a given date, it will return the row number that are within the From & To date in the attached worksheet.

Tks
Test.xlsm
0
AXISHK
Asked:
AXISHK
  • 3
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If the date is in cell D3, then the answer is

=MATCH(D3,A4:A78,1)+ROW(D3)
0
 
Martin LissOlder than dirtCommented:
Add this code to a module.

Sub FindRow()

Dim lngRow As Long
Dim dteFind As Date

dteFind = InputBox("Please enter the date")
With ActiveSheet
    For lngRow = 4 To .UsedRange.Rows.Count
        If dteFind <= .Cells(lngRow, 2) Then
            MsgBox "Found in row " & lngRow
            Exit Sub
        End If
    Next
End With
MsgBox "Date " & dteFind & " not found"
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Or you can return the row using a Function like this. The function will return row 0 if the date isn't found.

Usage:
Dim dte As Date

dte = "2/18/14"
MsgBox FindRow(dte)

Open in new window


Private Function FindRow(dteFind As Date) As Long

Dim lngRow As Long

With ActiveSheet
    For lngRow = 4 To .UsedRange.Rows.Count
        If dteFind <= .Cells(lngRow, 2) Then
            FindRow = lngRow
            Exit Function
        End If
    Next
End With
FindRow = 0
End Function

Open in new window

0
 
AXISHKAuthor Commented:
Tks
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now