MAKING THE VBA EXCEL CODE TAKE CARE OF THE NEW DATA ADDED TO THE SERIES

[Mponda]

I have two series data with 23 observation each. It start from 1998 to 2020. The series is updated to include the data for the ending year, for example the data for 2021 has to be added to the data series. I succeeded to write the following VBA excel code of obtaining regression slope and intercept with data ending to 2020;
Sub Linear_Regression()
n = 23
'create vectors
Dim x(23)
Dim y(23)
'input x & y points
For i = 1 To n
x(i) = Sheets("revenue").Cells(4 + i, 3)
y(i) = Sheets("revenue").Cells(4 + i, 4)
Next i

' Calculating cofficients
m = WorksheetFunction.Slope(y, x)
b = WorksheetFunction.Intercept(y, x)

'displaying data

Sheets("lregr").Cells(3, 3) = b
Sheets("lregr").Cells(3, 4) = m

End Sub

The above code give good result, but it is static to the sense that once the data in each series of x and y is added, they are not used by the code. How can I make the VBA code to take care of the added observation in each series?. Thank you in advance.
The below are the data used.

year	x	y
1998	2,632.6	33.2
1999	2,778.4	58.4
2000	2,958.6	53.2
2001	3,222.2	81.8
2002	3,453.5	84.9
2003	3,668.6	108.3
2004	3,876.4	117.5
2005	4,086.6	120.7
2006	4,341.2	136.9
2007	4,574.2	159.6
2008	4,813.9	188.4
2009	5,062.7	162.6
2010	5,350.0	171.3
2011	5,687.2	189.0
2012	5,969.5	196.0
2013	6,289.5	215.1
2014	6,685.4	200.3
2015	7,086.2	203.5
2016	7,566.0	256.5
2017	8,127.7	329.2
2018	8,703.5	323.6
2019	9,212.3	340.9
2020	9,722.5	436.2

 

[offthelip]

you just need to detect how many values there are in column C like this:

Sub Linear_Regression()
Dim x()
Dim y()
With Worksheets("revenue")
n = .Cells(Rows.Count, "C").End(xlUp).Row
'n = 23
'create vectors
ReDim x(n)
ReDim y(n)
'input x & y points
For i = 1 To n
x(i) = .Cells(4 + i, 3)
y(i) = .Cells(4 + i, 4)
Next i
End With
' Calculating cofficients
m = WorksheetFunction.Slope(y, x)
b = WorksheetFunction.Intercept(y, x)

'displaying data

Sheets("lregr").Cells(3, 3) = b
Sheets("lregr").Cells(3, 4) = m

End Sub

 

[Mponda]

you just need to detect how many values there are in column C like this:

Sub Linear_Regression()
Dim x()
Dim y()
With Worksheets("revenue")
n = .Cells(Rows.Count, "C").End(xlUp).Row
'n = 23
'create vectors
ReDim x(n)
ReDim y(n)
'input x & y points
For i = 1 To n
x(i) = .Cells(4 + i, 3)
y(i) = .Cells(4 + i, 4)
Next i
End With
' Calculating cofficients
m = WorksheetFunction.Slope(y, x)
b = WorksheetFunction.Intercept(y, x)

'displaying data

Sheets("lregr").Cells(3, 3) = b
Sheets("lregr").Cells(3, 4) = m

End Sub

Thank you very much sir. It worked and after adding observation it calculate new intercept and slope that is want I was wanting.