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:-

July	Hour
Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
01	20.2	23.8	26.4	28.2	29.1	30.0	31.1	32.0	32.5	33.2	32.7	32.2	30.8	29.8	28.7	28.0	27.4	27.2	26.5	26.3	25.9	24.4	21.6	20.2
02	21.6	24.2	27.7	28.6	29.1	31.1	31.6	32.4	33.7	33.7	32.9	32.6	31.5	29.5	29	28.5	28.4	27.9	27.3	26.9	27	26	25

<tbody>
</tbody>

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,