Match Date Search
 

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

Match Date Search
 

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" wrote in
message ...

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