Range Notation for Excel Functions in VBA
 

I am trying to perform calculations on data using the Excel function
SLOPE and INTERCEPT, but I am unable to find the correct notation fo
the ranges that I need.

I need something to the effect of:

mySlope
Application.WorksheetFunction.Slope(Cells(start_da ta,end_data)
Cells(start_time,end_time))

start_data, end_data, start_time, end_time have been determine
previously.

I know that the above does NOT work, but I need something similar tha
can give me the result I need.

Any help/ideas are much appreciated.
-Becki

--
Message posted from http://www.ExcelForum.com

Range Notation for Excel Functions in VBA
 

It all depends on what start_data, end_data, start_time and end_tim
are. If they are range addresses (like A1), use:

mySlope = Application.WorksheetFunction.Slope(Range(start_da ta & ":"
end_data),Range(start_time & ":" & end_time))

If they are row numbers for a known column (I'll use A and B), use:

mySlope = Application.WorksheetFunction.Slope(Range("A" & start_data
":A" & end_data),Range("B" & start_time & ":B" & end_time))



--
Message posted from http://www.ExcelForum.com

Range Notation for Excel Functions in VBA
 

Sorry...but it still doesn't work. I get the error:

Method "Range" of Object "_Global" faile

--
Message posted from http://www.ExcelForum.com

Range Notation for Excel Functions in VBA
 

What are the values in your variables (start_data, etc.)? How do yo
get them. This should be a snap once I know that.



--
Message posted from http://www.ExcelForum.com

Range Notation for Excel Functions in VBA
 

The values are row numbers and were found (not very elegantly) from th
following pieces of code.

Range("A1").Select
end_row = Cells.End(xlDown).Row

For i = 2 To end_row
Threshold = Application.Average(Cells(i, 2))
If (Cells((i + 1), 2) Threshold) Then
start_time = Cells((i + 2), 1)
start_time_row= Cells((i + 2), 1).Row
i = end_row
End If
End If
Next

temp = 0
For i = start_time_row To end_row
temp = Cells(i, 2).Value
If (temp <= Threshold) Then
stop_time_row = i
i = end_row
End If
Nex

--
Message posted from http://www.ExcelForum.com

Range Notation for Excel Functions in VBA
 

Try this:

Dim DataRange As Range
Dim TimeRange As Range

Set DataRange = Range("B" & start_data & ":B" & end_data)
Set TimeRange = Range("A" & start_time & ":A" & end_time)

myslope = Application.WorksheetFunction.Slope(DataRange, TimeRange)

It assumes your data is in column B and the times are in column A.
Plus, the time and data start and ends should be the same, so yo
really don't need both variables, just different columns.

Replace A and B above to get it to your specifications.



--
Message posted from http://www.ExcelForum.com