[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • 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 BurtonCommented:
If the date is in cell D3, then the answer is

=MATCH(D3,A4:A78,1)+ROW(D3)
0
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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