Solved

Excel VBA find the row number for a start date and end date in range

Posted on 2011-03-24
9
721 Views
Last Modified: 2012-05-11
If i had a range of dates in column B (sorted in date order)

what is the easiest way to find the row number for an input boxes start date and end date ?

Thanks
0
Comment
Question by:carlspywell
  • 4
  • 3
  • 2
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35205837
use

WorksheetFunction.Min(Range("B:B"))

and

WorksheetFunction.Max(Range("B:B"))

Saqib
0
 

Author Comment

by:carlspywell
ID: 35205868
To clarify i will have an input box asking for a start date and end date. i then need to find the 1st row with the start date and the last row with the end date.

0
 
LVL 17

Expert Comment

by:wobbled
ID: 35205932
here you go

Sub test()


Dim dt1 As Date, dt2 As Date
Dim str1 As String, str2 As String
Dim rng As Range


    dt1 = InputBox("enter start date")
    dt2 = InputBox("enter end date")
   
   

    For Each rng In Range("B1:B100")
   
        If CDate(rng.Value) = dt1 Then str1 = "Start date of " & dt1 & " is in row " & rng.Row
        If CDate(rng.Value) = dt2 Then str2 = "End date of " & dt2 & " is in row " & rng.Row
       
    Next rng
   
    MsgBox str1 & vbCrLf & str2

End Sub
0
 
LVL 17

Accepted Solution

by:
wobbled earned 250 total points
ID: 35205949
sorry slight change so you always get the first row where it finds it for the start date and the last row where it finds the end date as you asked in your questions


Sub test()


Dim dt1 As Date, dt2 As Date
Dim str1 As String, str2 As String
Dim rng As Range


    dt1 = InputBox("enter start date")
    dt2 = InputBox("enter end date")
   
    For Each rng In Range("B1:B100")
        If CDate(rng.Value) = dt1 Then
            str1 = "Start date of " & dt1 & " is in row " & rng.Row
            Exit For
        End If
    Next rng
   
    For Each rng In Range("B1:B100")
        If CDate(rng.Value) = dt2 Then str2 = "End date of " & dt2 & " is in row " & rng.Row
    Next rng
   
    MsgBox str1 & vbCrLf & str2

End Sub
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35205989
Wobbled, instead of looping you can use

            str1 = "Start date of " & dt1 & " is in row " & worksheetfunction.match(dt1,range("B:B"),0)

Saqib
0
 
LVL 17

Expert Comment

by:wobbled
ID: 35206042
Hi Ssagibh you are correct - the only reason I went for the loop was to pick up the first and last instances.  If I'm right, the match function will only find the first instance?

so a better way of writing my code would be

Sub test()


Dim dt1 As Date, dt2 As Date
Dim str1 As String, str2 As String
Dim rng As Range


    dt1 = InputBox("enter start date")
    dt2 = InputBox("enter end date")
   
    str1 = "Start date of " & dt1 & " is in row " & worksheetfunction.match(dt1,range("B:B"),0)
   
    For Each rng In Range("B1:B100")
        If CDate(rng.Value) = dt2 Then str2 = "End date of " & dt2 & " is in row " & rng.Row
    Next rng
   
    MsgBox str1 & vbCrLf & str2

End Sub
0
 

Author Comment

by:carlspywell
ID: 35206084
I get the Error "Unable to get the match property of the worksheetFunction class" on the line
str1 = "Start date of " & dt1 & " is in row " & WorksheetFunction.Match(dt1, Range("B:B"), 0)
0
 

Author Comment

by:carlspywell
ID: 35206121
Attache is the sample from my workbook.

I looked for start date = 02/01/2008 and end date = 02/01/2008

but get error.

Carl
test.xlsm
0
 

Author Closing Comment

by:carlspywell
ID: 35207613
@ssaqibh This throws up an error as the loop suggested works fine.
Cheers
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now