VBA Declare date/time data

Question

I ve got hourly data and when I try to execute the following code I get an runtime error 91.The format of my data in the CYC sheet is for example #07/07/2009 23:00:00# (at row 194), but when I enter this to dt it automatically converts it to #7/7/2009 11:00:00 PM#. (please note that shtCYC and shtCo have been declared and set).

Dim dt As Date
dt = #7/7/2009 11:00:00 PM#
    Do
    shtCYC.Activate
    'finds the day
    Set rng = shtCYC.Range("A3:A1514").Find(dt, , xlValues)
    'copies the dates
    shtCYC.Range("A" & rng.Row - 191 & ":A" & rng.Row + 24).Copy (this is where the debug highlights)
    shtCO.Range("B10").PasteSpecial Paste:=xlPasteValues

Anyone got any ideas..? Many Many thanks!

Answer 1

Well that is not the only problem that I see. See the code below.

1. To find dates you have to use DateValue because of various formatting reasons.
2. You need to check if a value was found
3. You need to check if the rng.Row falls in a specific range

I have explained it in comments. Let me know if you still have questions.

Sub Sample()
    Dim dt As Date
    Dim shtCYC As Worksheet
    Dim Rng As Range

    dt = #7/7/2009 11:00:00 PM#

    Set shtCYC = ActiveSheet '<~~ Change this to the relevant sheet

    With shtCYC
        Set Rng = .Range("A3:A1514").Find(what:=DateValue(dt), LookIn:=xlFormulas)

        '~~> Check If match found
        If Not Rng Is Nothing Then
            '~~> This Check is required because what if the rng.row is <=191 or >=1048552?
            '~~> I understand that you have mentioned the range as "A3:A1514"
            '~~> But tom if you use .Cells then?
            '~~> Rng.Row - 191 / Rng.Row + 24 will give you error in that case
            If Rng.Row > 191 Or Rng.Row < (.Rows.Count - 24) Then
                .Range("A" & Rng.Row - 191 & ":A" & Rng.Row + 24).Copy
                'shtCO.Range("B10").PasteSpecial Paste:=xlPasteValues
            End If
        Else
            MsgBox "Match Not Found"
        End If
    End With
End Sub

Tested in Excel 2013. My Worksheet looks like this.