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

#### Mponda

##### New Member
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### offthelip

##### Well-known Member
you just need to detect how many values there are in column C like this:
VBA Code:
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

##### New Member
you just need to detect how many values there are in column C like this:
VBA Code:
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.

Replies
4
Views
121

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,978
Messages
5,767,426
Members
425,412
Latest member
andrealp4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back