Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What does the ! notation mean in excel financial formulas? | Excel Worksheet Functions | |||
Set Notation in Excel | Excel Discussion (Misc queries) | |||
Expand IP Address range from slash notation to individual IPs... | Excel Discussion (Misc queries) | |||
scientific notation to display 1.8 x 10-5 in Excel | New Users to Excel | |||
Using Range with R1C1 notation in a macro | Excel Programming |