auto fill series linear growth trend between non-adjacent cells in a column

Hi,

I am a building energy consultant and am trying to create an hourly weather file from annual data (in other words, taking 365 days of data and spreading it over 8760 hours in the year).

I have the lowest temperature (recorded at around 6 am) and the highest temperature (recorded at 3 pm) and need to fill the in-between cells with a linear growth series.

QUESTION: How can I auto-fill the empty cells with a series between two populated cells. Especially when two cells are 8 rows apart and the next two cells are 14 rows apart.

I am attaching the Excel file herewith for your perusal and response.

Thank you for providing a platform such as this. I shall be grateful for any possible help in this regard.

Kindly

Gaurav

THE_Weather_File.xlsm

hi,

I had a look at your file, but didn't make that much out of it.

However, maybe you can adapt the following code to what you want.
If you have some numbers scattered down columnA with spaces between, the code will linear interpolate the spaces.

Sub linear_inbetween()

Dim c As Range
Set c = Range("A1")
Do
Set d = c.End(4)
If c(2) = "" Then
    Do
        c(2) = c + (d - c) / (d.Row - c.Row)
        Set c = c(2)
    Loop Until c = d(0)
End If
Set c = d
Loop Until d.End(4).Row = Rows.Count

End Sub

Thanks for the quick reply. I'm sorry it isnt clear in the sheet. The trouble starts after row 559 in Column E (I actually sat and used the whole Fill>Series...>Linear>trend option to fill the earlier rows in Coumn E :p )

The macro comes up with an error in the c(2) = c + (d - c) .... line. Can you help please? I am very new to using macros.

Thanks for taking the trouble.

the problem is the interpolation code only works with numerical values
a number of your apparently blank cells seem not in fact to be truly empty cells.

If you don't mind setting your columnE to values only, then try the following slightly modified code. it works OK for me

Sub linear_inbetween2()

Range("E:E").Value = Range("E:E").Value
Dim c As Range, d as Range
Set c = Range("E1")
Do
Set d = c.End(4)
If c(2) = "" Then
    Do
        c(2) = c + (d - c) / (d.Row - c.Row)
        Set c = c(2)
    Loop Until c = d(0)
End If
Set c = d
Loop Until d.End(4).Row = Rows.Count

End Sub

I guess you get this a lot, but god bless you. Thanks endlessly. You just solved something that i've spent three days on, in seconds. I owe you big time. :D

Thanks for the feedback.

Glad to be of some help.