Problems with Selection.find

[stagio]

Hello everyone. Newer to VBA and have very litttle instruction so don't laugh.

I am trying to take a daily report with multiple skills and move this data into a yearly archive file for each skill. Sounded simple enough but I'm stuck. I keep getting an object error even when I simply record the macro and try to run it. Here is the code-- any idea's?

Dim rdate As String
Dim skill As String
Dim fpath As String
Dim spath As String
Dim wbname As String
Dim csname As String
Dim dbname As String
Dim csfile As String
Dim Drange As Range
Dim xdate As String



Sub DashboardUPDT()
wbname = ActiveWorkbook.Name
Sheets("DB").Activate
fpath = Cells(1, 1) 'dashboard file path defined
spath = Cells(2, 1) 'Daily report path defined
rdate = InputBox("Enter Report Date") ' reporting date in question
rdate = Format(rdate, "MM-DD-YYYY") ' reformat for use in file path
xdate = Format(rdate, "MM/DD/YYYY") ' reformat date for search in dashboard range
csname = spath & "Daily Summary - " & rdate & ".xlsx" ' define file path
Workbooks.Open (csname) ' open daily report
csfile = ActiveWorkbook.Name ' define daily workbook variable
For r = 5 To 52 ' time to loop
If Cells(r, 3) <> "" Then ' if skill isn't blank proceed
If Cells(r, 1) <> "x" Then 'x in col A indicates a total row
skillx = Cells(r, 3) ' define skill
skpath = fpath & skillx & ".xlsx" ' define dashboard path
Workbooks.Open (skpath) ' open dashboard
dbfname = ActiveWorkbook.Name ' define dashboard variable
Else
GoTo Nextrow ' if not a skill move on to next row
End If
End If
Workbooks(csfile).Activate ' activate daily summary
Range("J" & r & ":" & "V" & r).Select ' copy range of data associated with skill
Selection.Copy ' copy it
Workbooks(dbfname).Activate ' activate dashboard for skill in question

Range("c:c").Activate ' activate search range
'***************************Error is driving me nutz********************

Selection.Find(What:=xdate, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate ' ERROR ERROR code 91
On Error Resume Next
ActiveCell.Offset(0, 1).Select ' select cell ajacent to date found
Nextrow:
Next r
End Sub

 

[AlphaFrog]

I think your issue may be with how Excel evaluates Dates. The problem is you prompt the user for a date as a text string. Then you do a search for a text-date in a column of serial dates. A text string that "looks" like a date is not the same as a serial date that you want to .Find

Here's a primer on How Excel Stores Dates And Times

The quick fix may be to simply convert the text-date into a serial date.

Selection.Find(What:=[COLOR="Red"]CDate([/COLOR]xdate[COLOR="Red"])[/COLOR], After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

 

[stagio]

ray:I wondered if it was having problem with the date. That would also eliminate the need to reformat the date and help me clean up the code a bit. I SO appreciate your help with this!

Thank you Thank you Thank you Thank you!

 

[AlphaFrog]

Newer to VBA and have very litttle instruction so don't laugh.

...That would also eliminate the need to reformat the date and help me clean up the code a bit.

You're welcome and I think you know more than you let on.

 

[stagio]

I hate to think I have any talent with VB when I see other peoples code, espceially when I spend 3 or 4 hours on a problem like this where 5 characters fixed it. I can honestly say, all of my research never would adress date format in a .find.

Loving this forum!

Thanks again

Steve