# 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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### 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
253

### Forum statistics

1,186,371
Messages
5,957,473
Members
438,307
Latest member
bigmike1720 ### 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