vba offset

tvanduzee

Board Regular
Joined
Mar 14, 2008
Messages
86
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:
Code:
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,383
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
 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.
 
Last edited:

tvanduzee

Board Regular
Joined
Mar 14, 2008
Messages
86
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,969
Members
414,353
Latest member
ljhan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top