Help with VBA

[dpchris]

I'm very new to VBA and I would really appreciate some help.

I have a lot of data that's organized as follows:

x y
0 y1
1 y2
2 y3
3 y4
4 y5
....
9 y10
0 y11
1 y12
....
9 y20
0 y21
....

I have created formulas that run regressions on these sets of data.
The x values represent time and right now they all range from 0-9, but
in the future more data points will be added. What I want to do is
write a VBA macro that will allow me to determine how many data points
I want to regress on.

Right now the regression is set to run on all tendata points. My
thought was that the user could input a value, k, into a certain cell
and when the macro is run it will regress on only those last k points.
For example, if the user inputs the value 6 and runs the macro, the
regression will regress on points 4, 5, 6, 7, 8, and 9, i.e. the last
six points.

Since more points can be added in the future, however, the last value
won't be in the same row. So I'm thinking about running a loop that
checks to see if the value of the x is increasing. Once it sees that
the value starts over again at 0, it goes back up and that is the last
value in the list.

An example of one of the formulas I'm using is as follows: =COVAR(LN($B
$4:$B$13),$A$4:$A$13)/DEVSQ($A$4:$A$13)*COUNT($B$4:$B$13)

Where $B$4:$B$13 are the y values and $A$4:$A$13 are the x values,
with Row 13 being the last value in the list (my code will see this
because the value in A14 is 0). When I run the macro with the value of
6, I want it to go in and change the values to $B$8:$B$13 and $A$8:$A
$13.

Thanks in advance for all your help!

 

[Joel]

You appearr to being formulas on the spreadsheet rather than VBA code. It
looks like you want a custom VBA functtion that you can call from your
worksheet.

This functtion you can enter the first cell of a row and it will count the
number of items in the row

= NumberEntries(A10)

Function NumberEntries(Target As Range)

LastRow = Cells(Rows.Count, Target.Column).End(xlUp).Row

NumberEntries = LastRow - Target.Row + 1
End Function

 

[Joel]

You can also do something like this. Pass the first cell. Notice the period
before LN and Devsq they are worksheetfunction using the WITH.

=CovarRow(B7)

Function CovarRow(Target As Range)

LastRow = Cells(Rows.Count, Target.Column).End(xlUp).Row
Col1range = Range(Target, Cells(LastRow, Target.Column))

With WorksheetFunction
CovRow = (LastRow - Target.Row + 1) * _
.Covar(.Ln(Col1range.Offset(0, 1)), _
.Ln(Col1range)) / _
.DevSq(Col1range)
End Function