Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

darvistor (TechnicalUser) (OP)
2 Oct 07 18:41
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!
 
SkipVought (Programmer)
2 Oct 07 18:49





Hi,

1.  You posted no code.

2.  Why use VBA?

Skip,

glasses 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!tongue

darvistor (TechnicalUser) (OP)
2 Oct 07 19:32
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.

CODE

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 (Programmer)
2 Oct 07 19:46




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

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

Skip,

glasses 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!tongue

darvistor (TechnicalUser) (OP)
2 Oct 07 21:04
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, D9:D11). 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 D9:D11.

The problem is that Excel and VBA dont seem to like the data type of my variables.
SkipVought (Programmer)
2 Oct 07 21:09




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,

glasses 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!tongue

darvistor (TechnicalUser) (OP)
2 Oct 07 22:01
Thanks Skip. This is what the final result looks like

=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!
combo (TechnicalUser)
3 Oct 07 3:57
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close