CODEWITHSUNDEEP
vbaexcel
Argyris
Argyris

Reputation: 53

VBA Declare date/time data

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!

Upvotes: 4

Views: 18638

Answers (2)

user4039065
user4039065

Reputation:

You might have more success locating the datetime by using the worksheet's MATCH function. I often have issues with the Range.Find method when searching for values containing times.

Dim dt As Date, rw As Variant
Dim shtCYC As Worksheet, shtCO As Worksheet

dt = #7/7/2009 11:00:00 PM#
Debug.Print dt

Set shtCYC = Worksheets("Sheet4")   '<~~ set the source worksheet
Set shtCO = Worksheets("Sheet5")    '<~~ set the target worksheet

With shtCYC
    'finds the row containing the datetime value
    rw = Application.Match(CDbl(dt), .Columns(1), 0)
    If Not IsError(rw) Then
        'dt was found, transfer the block of values
        If rw > 194 Then
            shtCO.Range("B10").Resize(216, 1) = _
                .Cells(rw, 1).Resize(216, 1).Value
        Else
            Debug.Print rw & " not large enough to encompass all values."
        End If
    Else
        Debug.Print dt & " not found."
    End If

End With

Note that this is using direct value transfer rather than Copy & Paste. It is a more efficient method of transferring xlPasteValues as it does not involve the clipboard.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

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.

enter image description here

Upvotes: 4

Related Questions