Auto Filling Formulas & Custom Intervals

[fnpb93]

Hello all! New here and would appreciate some help if any kind souls here are willing to help out.

Currently, I have two columns of data set, namely the X and Y coordinates of a certain trend graph. Do note that the X coordinates are not equally spaced apart, hence, the data is quite messy. Furthermore, the y coordinates constantly go up and down hence it is not by any means a linear trend graph.
Using interpolation (FORECAST/INDEX functions), I managed to estimate the values of the y coordinates given a certain interval. My X value varies from 0 to 180, and I managed to use interpolation as previously mentioned to find the y coordinates when x =0 , 1, 2, 3... 180 (interval =1)

Right now, I am wondering how can I do about specifying a specific interval for my x values.
This means in H1, the user should be able to specify an interval (e.g. 0.5). Upon pressing/executing the macro, G2 should start from 0 and should fill down to the value of 180 with an interval of 0.5. This means G3= 0.5, G4= 1, G5=1.5.... G361 = 180 (if I'm not wrong)
Since I will also be doing interpolation, is there are also anyway to fill up the H column automatically when filling up the G column at the same time? The H column's formula takes an input from the G column in the same row. (For example H1=G1)

Thank you so much!

 

[Engberg]

Someone will be able to help you with this, but they will want to know the formula. Could you post the formula that you would use in for example H2 and H3?

 

[Alex Blakenburg]

As @Engberg pointed out the macro will need to know what formula to put in.
The below assumes you have put in an starting number in G2 and a formula in H2.
It also needs to know the stop value which I have put in J1.

20220114 AutoFill Set increments.xlsm
	G	H	I	J
1	Interval =	1	Stop Value =	180
2	0	0
3	1	1
4	2	2
5	3	3
6	4	4
7	5	5
8	6	6
9	7	7
10	8	8
11	9	9
12	10	10
Sheet1

Cell Formulas
Range	Formula
H2:H12	H2	=G2

Sub AutoFillInterval()

    Dim sht As Worksheet
    Dim rng As Range
    Dim Increment As Double
    Dim StopValue As Double
    
    Set sht = ActiveSheet
    Set rng = sht.Range("G2:G" & sht.Range("G" & sht.Rows.Count).End(xlUp).Row)
    rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 2).ClearContents
    
    Increment = sht.Range("H1").Value
    StopValue = sht.Range("J1").Value
    
    With sht.Range("G2")
        If .Value = "" Then .Value = 0
    End With
    
    sht.Range("G2").DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step _
                :=Increment, Stop:=StopValue, Trend:=False

    Set rng = sht.Range("G2:G" & sht.Range("G" & sht.Rows.Count).End(xlUp).Row)

    rng.Offset(0, 1).Formula = rng.Cells(1, 1).Offset(0, 1).Formula

End Sub

 

[fnpb93]

Thank you all for the help. Have managed to do out something of my own after much thinking and tinkering around!

 

[Alex Blakenburg]

Thanks for letting us know. If you want to post what you ended up doing you can mark it as the solution.