Match Date Search

[murfyang]

Hi there, new to excel forum & also slow learner in VBA. Am doing a match date search & then copying that range of price with the date range to another worksheet. used this code as suggested but unable to work, it end with a "Start date not found" msg.
My date is in column B formatted as dd/mm/yyyy. Have tried adding the cdate to the startdate & enddate & also changing the 0 to 1 in the match function, but still the same.
Any one can help, thanks greatly

Please Login or Register  to view this content.

[Tom Ogilvy]

The deleted lines were meant to be deleted - don't add them back.


Option Explicit

Sub FindDates()
Worksheets("Prices").Select

On Error Goto errorHandler
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
Dim rng1 As Range, rng2 As Range
Dim x As Variant, r As Range

startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then Exit Sub
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then Exit Sub
With Worksheets("Prices")
Set r = .Range(.Cells(1, 2), .Cells(65536, 2).End(xlUp))
End With
x = Application.Match(clng(cdate(startDate)), r, 0)
If IsNumeric(x) Then
startRow = x
Else
MsgBox "Start date not found"
End
End If

x = Application.Match(clng(cdate(stopDate)), r, 0)
If IsNumeric(x) Then
stopRow = x
Else
MsgBox "End date not found"
End
End If

--
Regards,
Tom Ogilvy

"murfyang" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there, new to excel forum & also slow learner in VBA. Am doing a
> match date search & then copying that range of price with the date
> range to another worksheet. used this code as suggested but unable to
> work, it end with a "Start date not found" msg.
> My date is in column B formatted as dd/mm/yyyy. Have tried adding the
> cdate to the startdate & enddate & also changing the 0 to 1 in the
> match function, but still the same.
> Any one can help, thanks greatly
>
>
>
> Code:
> --------------------
> Option Explicit
>
> Sub FindDates()
> Worksheets("Prices").Select
>
> On Error Goto errorHandler
> Dim startDate As String
> Dim stopDate As String
> Dim startRow As Integer
> Dim stopRow As Integer
> Dim rng1 As Range, rng2 As Range
> Dim x As Variant, r As Range
>
> startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
> If startDate = "" Then End
> stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
> If stopDate = "" Then End
> startDate = Format(startDate, "dd/mm/yyyy")
> stopDate = Format(stopDate, "dd/mm/yyyy")
> With Worksheets("Prices")
> Set r = .Range(.Cells(1, 2), .Cells(65536, 2).End(xlUp))
> End With
> x = Application.Match(startDate, r, 0)
> If IsNumeric(x) Then
> startRow = x
> Else
> MsgBox "Start date not found"
> End
> End If
>
> x = Application.Match(stopDate, r, 0)
> If IsNumeric(x) Then
> stopRow = x
> Else
> MsgBox "End date not found"
> End
> End If
> --------------------
>
>
> --
> murfyang
> ------------------------------------------------------------------------
> murfyang's Profile:
http://www.excelforum.com/member.php...o&userid=26702
> View this thread: http://www.excelforum.com/showthread...hreadid=471693
>