## SLOPE function in VBA

## SLOPE function in VBA

(OP)

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 D9:D11 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!

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 D9:D11 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!

## RE: SLOPE function in VBA

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! andMany are Cold, but Few are Frozen!}## RE: SLOPE function in VBA

## CODE

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

## RE: SLOPE function in VBA

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.

## CODE

Skip,

_{ When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...Tooth Company Freeze a Crowd! andMany are Cold, but Few are Frozen!}## RE: SLOPE function in VBA

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 D9:D11.

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

## RE: SLOPE function in VBA

You can use the OFFSET function & COUNTA function to dynamically define the last three cells in a range. Don't need VBA...

=offset(Sheet1!$A$1,Counta(Sheet1!A:A)-3,0,3,1)

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! andMany are Cold, but Few are Frozen!}## RE: SLOPE function in VBA

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

Works like a charm!

## RE: SLOPE function in VBA

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