Problems using .Find for dates in formulas

[Dan_R]

Hi,
I'm hoping someone will be able to help me out with a problem that's driving me mad at the moment. I have a macro which I use to update some daily statistics by pulling data from one file, finding the relevant date in column A, then pasting a row of date next to it. The spreadsheet already has the dates typed in for the whole year, which is ok, but not a very good solution as I'll have to duplicate this for several years on multiple worksheets, it would be much better if I could have the dates as a formula. When I try to put the date in column A as a formula instead of a manually entered date, the macro cannot find it and I get the error 'Run-time error '91' Object variable or With block variable not set'. I know that I've seen other similar posts on here but I can't find anything that makes it work. I know it shouldn't matter what format the date is in as excel recognises it as a date whatever, and I've tried changing the format several times and it dosen't seem to make a difference - but it has to be just dd for this sheet so although it displays '01' for example, the actual cell value is '01/07/2015', here's an example of how the spreadsheet looks:-

[TABLE="width: 1163"]
<tbody>[TR]
[TD]July
[/TD]
[TD]Hour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]01
[/TD]
[TD]20.2
[/TD]
[TD]23.8
[/TD]
[TD]26.4[/TD]
[TD]28.2[/TD]
[TD]29.1[/TD]
[TD]30.0[/TD]
[TD]31.1[/TD]
[TD]32.0
[/TD]
[TD]32.5[/TD]
[TD]33.2[/TD]
[TD]32.7[/TD]
[TD]32.2[/TD]
[TD]30.8[/TD]
[TD]29.8[/TD]
[TD]28.7[/TD]
[TD]28.0[/TD]
[TD]27.4[/TD]
[TD]27.2[/TD]
[TD]26.5[/TD]
[TD]26.3[/TD]
[TD]25.9[/TD]
[TD]24.4[/TD]
[TD]21.6[/TD]
[TD]20.2[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD]21.6
[/TD]
[TD]24.2
[/TD]
[TD]27.7[/TD]
[TD]28.6
[/TD]
[TD]29.1[/TD]
[TD]31.1[/TD]
[TD]31.6[/TD]
[TD]32.4
[/TD]
[TD]33.7[/TD]
[TD]33.7[/TD]
[TD]32.9[/TD]
[TD]32.6[/TD]
[TD]31.5[/TD]
[TD]29.5[/TD]
[TD]29[/TD]
[TD]28.5[/TD]
[TD]28.4[/TD]
[TD]27.9[/TD]
[TD]27.3[/TD]
[TD]26.9[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]
And here's the bits of code which I think are relavant:-

Dim MyDate, DataDate As Date
Dim MyDay, sDate As String

MyDate = Now
DataDate = Format(MyDate - 1, "dd/mm/yy")

'the bit to select which date gets imported

sDate = InputBox("Choose date (dd/mm/yy), "Date to import", DataDate)

If sDate = "" Then
MsgBox "Cancel Pressed" & Chr$(13) & "No data imported"
GoTo THEEND
End If

If IsDate(sDate) Then
DataDate = Format(sDate, "dd/mm/yy")
GoTo DATESELECTED
Else
MsgBox "Bad date entry, please try again"
DataDate = Format(MyDate - 1, "dd/mm/yy")
GoTo SELECTDATE
End If

-------
'the find bit
Set Rng = Range("A:A").Find(DateValue(DataDate), LookIn:=xlFormulas, lookat:=xlWhole)
Rng.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues

Sorry, I'm new here so if there's any obvious bits of information that I've missed out please let me know - otherwise thanks in advance if anyone can help me out.

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

You could try MATCH instead of FIND:

=MATCH(DATEVALUE("1/2/15"),A:A,0)

or in VBA:

Sub myMatch()

    Dim rc As Variant
    Dim dt As Date
    dt = Date
    
    rc = Application.Match(CLng(dt), Range("A:A"), 0)
    If IsNumeric(rc) Then
        Debug.Print "Row number is " & rc
    Else
        Debug.Print "Date not found"
    End If

End Sub

 

[Dan_R]

Thanks, that's great - it's working now
I'd tried to use the Application.Match and CLng before, but got a bit confused with it. As I know that the dates are always going to be in column A I've just used:

Range("B" & rc).Select
Selection.PasteSpecial Paste:=xlPasteValues

to paste he values in the rigt place
Thanks again

 

[RickXL]

Good news - thanks for letting me know.

Regards,