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

Mponda

New Member
Joined
Aug 23, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.
yearxy
19982,632.633.2
19992,778.458.4
20002,958.653.2
20013,222.281.8
20023,453.584.9
20033,668.6108.3
20043,876.4117.5
20054,086.6120.7
20064,341.2136.9
20074,574.2159.6
20084,813.9188.4
20095,062.7162.6
20105,350.0171.3
20115,687.2189.0
20125,969.5196.0
20136,289.5215.1
20146,685.4200.3
20157,086.2203.5
20167,566.0256.5
20178,127.7329.2
20188,703.5323.6
20199,212.3340.9
20209,722.5436.2
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,166
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 23, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,340
Messages
5,746,186
Members
423,998
Latest member
eakenila

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top