SLOPE function in VBA

[darvistor]

Hello all,

I am new to the forums and am trying to resolve a VBA error I keep getting using the SLOPE function. (Note: I looked at the other topics and could not get the solutions to work for my issue)

I am trying to analyze a set of data in Excel that varies week to week.

Yield 1 Yield 2 Final Yield
1 99.5% 99.5% 99.0%
2 98.2% 98.2% 96.4%
3 97.5% 97.5% 95.1%
4 98.2% 98.2% 96.4%
5 99.0% 99.0% 98.0%
6 99.3% 99.2% 98.5%
7 99.0% 99.3% 98.3%
8 99.2% 99.7% 98.9%
9 99.4% 99.8% 99.2%
10 99.5% 99.8% 99.3%

Cellcount RangeStart RangeEnd
10 9 11
Xrange Yrange Slope
A9:A11 D911 0.001993


I want to calculate the SLOPE of the last 3 data values(Final Yield) so that I can trigger a trend indicator that shows me if I am trending up or down. Each week a new set of data points is added to the spreadsheet.

I was able to use the COUNTIF and CONCATENATE functions to create the ranges needed for the SLOPE formula, but I can't figure out a way to use these text strings in the formula because they aren't the right data type.

I have tried to calculate the SLOPE using VBA and defining the different variables, but I get to the same point in VBA where the SLOPE function does not accept the text string entered.

Any help would be greatly appreciated.

Thanks!

 

[SkipVought]

Hi,

1. You posted no code.

2. Why use VBA?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[darvistor]

Here is the very simple code I tried to use in VBA. The only reason I tried VBA is because I could not get the SLOPE function to work in Excel pointing to the two range values (A16 and B16). I thought it would work in VBA, but I get the following error: Unable to get the Slope property of the WorksheetFunction class.

Sub Button2_Click()

Dim Xrange As Variant
Dim Yrange As Variant
Dim cSlope As Double

Xrange = Range("A16")
Yrange = Range("B16")

cSlope = Application.WorksheetFunction.Slope(Xrange, Yrange)
Range("D16") = Slope

End Sub

 

[SkipVought]

If you can't get a SPREADSHEET function to work in a SPREADSHEET...

hmmmmmm?

I don't agree with your approch.

The X-Range is NOT Range("A16"): rather its Range("A9:A11") or something similar.

Range("D16") = Application.WorksheetFunction.Slope([A9:A11],[B9:B11])

I get 2.5

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[darvistor]

Ok, some clarifications. I can get the SLOPE function to work in Excel if I manually select the last three sets of data (A9:A11, D911). Because the data set changes every week and the number of cells added also varies, I didnt want to hardcode the ranges and I don't want to manually select the ranges every week.


I used the Concatenate function in Excel to create the ranges and placed them in cells A16 and B16. These cells update automatically when new data is added to the data set.

The Xrange variable points to cell "A16" which has the string A9:A11.

The Yrange variable points to cell "B16" which has the string D911.

The problem is that Excel and VBA dont seem to like the data type of my variables.

 

[SkipVought]

You can use the OFFSET function & COUNTA function to dynamically define the last three cells in a range. Don't need VBA...
[tt]
=offset(Sheet1!$A$1,Counta(Sheet1!A:A)-3,0,3,1)
[/tt]
is the X-Range.

Check Excel Help on this function.



Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[darvistor]

Thanks Skip. This is what the final result looks like

=SLOPE(OFFSET($D$1,COUNTA(D)-3,0,3,1),OFFSET($A$1,COUNTA(A:A)-3,0,3,1))

Works like a charm!

 

[combo]

Your code works with:

cSlope = Application.WorksheetFunction.Slope(Range(Xrange), Range(Yrange))
Range("D16") = cSlope

It breaks in case of inproper data entry, so Skip's pure spreadsheet solution is more stable.

combo