PDA

View Full Version : Using Find to Search for a Date



TurnpikeGate
08-05-2008, 10:55 AM
I need to extract dates from a pivot table( the worksheet has a couple pivot tables in it) and put them in another worksheet. I am doing a Find command to look for a date but i would like it to look for a whole month and select that month from the list and copy it into the other work book. As well i am not ever sure how to get the find to be specific to one column. here is the Code i am working from:

Sub FindDate()
Dim strdate As String
Dim rCell As Range
Dim lReply As Long

Sheets("RNK PIVOT TABLES").Select

strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _
Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)

'Cancelled
If strdate = "False" Then Exit Sub

strdate = Format(strdate, "Short Date")

On Error Resume Next
Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
On Error GoTo 0

If rCell Is Nothing Then
lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)
If lReply = vbYes Then Run "FindDate":
End If
End Sub

Bob Phillips
08-05-2008, 11:55 AM
As well i am not ever sure how to get the find to be specific to one column.



Set rCell = Columns("C:C").Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)