Trying to calculate a 1hr range within the last 12hrs of a range with VBA

Issue

So I am trying to calculate a test temp for 1hr from the last 12 hours of data. Sometimes this data can be several days some times it can be only 24hrs. The statements seem to select the 1hr range within the last 12 of the data set, however the formula in J3 returns a -1 no matter what the data is. If I fill column J with value 5 for 300 rows it was returning -1. Some iterations of the code I tried were returning 0. Each row is 5 minutes so that is where the offset values come from for the 1hr.

This is what I have so far, and I am not sure what the issue is. I am very new to VBA.

Sub TestTemp_Cal()
    
    Dim LastRow As Long
    Dim HrRng As Long
    
    'Selecting the 1hr range in the last 12 hrs of data
    LastRow = Application.ActiveSheet.Range("J6").End(xlDown).Select
    
    HrRng = Range(ActiveCell.Offset(-27, 0), ActiveCell.Offset(-39, 0)).Select
     
    'Calculating the Test Temp
    Range("J3").Formula = "=ROUND(AVERAGE(" & HrRng & "),0)"
     
End Sub

Would love some insight on this.

Solution

Just guessing without seeing any actual data…

Sub TestTemp_Cal()
    
    Dim ws As Worksheet, c As range, rng As range

    Set ws = Activesheet
    Set c = ws.Cells(Rows.Count, "J").End(xlUp)

    If c.Row > 44 Then 'make sure you can offset at least 39 rows
        Set rng = c.Offset(-39).Resize(12) '39 rows up and resize to 12 rows
        ws.Range("J3").Formula = "=ROUND(AVERAGE(" & rng.address(False, False) & "),0)"
    End If
    
End Sub

Answered By – Tim Williams

Answer Checked By – Gilberto Lyons (AngularFixing Admin)