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.
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)