vba offset

[tvanduzee]

Hello
I am trying to write some vba for my worksheet.
There are 89000 + rows in the sheet. This data will change each month.
I have some code that will itterate thought the 1st column (which is formatted and contains dates and times).
What I am trying to do is find a 24 hr period. So, I know when the day changes, the max value in the 2nd column will be extracted for that period.
This will itterate as long as hour(tRng < 24).
However, I cannot find what the problem in the code is:

Sub getranges()
Dim tRng As Range
Dim vRng As Range
Dim lCell

lCell = Sheets("summary").Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("Summary")
Set tRng = Range("A2:A" & lCell)
Set vRng = Range("B2:B" & lCell)

w = 0

    For Each cll In tRng
    w = w + 1
'Makes sure the data is all for the same year.
        If Year(cll.Value) = 2015 Then
'Checks the cell before to ensure the data is for the same day (up to 24 hrs)
        If Day(cll) = Day(cll.Row - 1) Then  'This is fine the first time through, 2nd time through it fails with false although my data says its true.
        'If Day(cll) = Day(cll.Offset(-1, 0))  'This does not work (type mismatch) and I can not see why it wouldn't work it records the first value (31) in my data, but the previous cell is recorded as  1). Although my data says it is 31
                                                         ' what I'm trying to do is ensure the current value of the cell (day) is the same day as the cell before it).

'Day, month and hour holders for possible reference later in the code.
            x = Month(cll.Value)
            y = Day(cll.Value)
            Z = Hour(cll.Value)
                    
        End If
        End If
    Next cll
    End With
End Sub

Every 30 seconds a new value is recorded throughout the day (although its not uncommon for it not to record for sometimes 2 or 3 minutes.)
Any suggestions?
Or if you have a better way of doing this, I'd be open to hearing it too.

Thank you
Terry

 

[dmt32]

Hi,
With such large data set filtering would most likely be much quicker for you but don’t have much time at moment.
Not tested but see if this tweak to your existing code helps you.

 Sub getranges()    Dim tRng As Range, vRng As Range
    Dim cll As Range
    Dim lCell As Long
    Dim r As Integer
    
    
    With Worksheets("Summary")
         lCell = .Range("A" & Rows.Count).End(xlUp).Row
        Set tRng = .Range("A2:A" & lCell)
        Set vRng = .Range("B2:B" & lCell)
    End With




    For Each cll In tRng
        'Makes sure the data is all for the same year.
        If IsDate(cll.Value) And Year(cll.Value) = 2015 Then
            'Checks the cell before to ensure the data is for the same day (up to 24 hrs)
            If Day(cll.Value) = Day(cll.Offset(r, 0).Value) Then
        
            'Day, month and hour holders for possible reference later in the code.
            x = Month(cll.Value)
            y = Day(cll.Value)
            Z = Hour(cll.Value)
            
            End If
        r = -1
        End If
    Next cll
    
End Sub

You were getting type mismatch error with offset(-1,0) as presumably, row 1 is a header row not a date? I have added a crude fix to resolve but perhaps someone else here may be able to offer better solution.
Hope Helpful

Dave.

 

[tvanduzee]

Thank you Dave.
For one reason or another, excel is not seeing my dates as dates, so I had to rem out the if isdate line. Other than that, it works great... thank you. Putting the r = -1 at the end was a stroke of genius.. work just the way I wanted.
Assuming I have 1 month of data, can I modify the code to begin the code again until the next 24 hr period is completed? (for the whole month, 24 hrs at a time).. I need to find the max per day for the month, then a max for the month. I can place this is a cell at the top of the sheet and have already begun the coding of that, Im using D2 as my start Cell for the heading (day of the month), then using the offset (0,1) to begin a list of the hours 00:00:01 to 01:00:00, 02:00:00 etc up to 11:00:00.

Again,
Thank you Dave, much appreciated.

Terry