Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define variable range
Hi
I'm not even sure this is possible so advice would be welcome! I need to define a y range based on two variable x parameters, x1 an x2. To give an example, x values ranging as integers from 4 to 16 are i column A (A1:A13) on my spreadsheet, corresponding y values in colum B. I have listed x1 in cell E1 and x2 in cell F1. x1 = 0 and x2 = 1 for this particular example. If 0<=x<12, I want to define the corresponding y range from column B. Here it would be the range B1:B8. Any suggestions -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define variable range
=Offset(B1,E1,0,F1-4,1)
Might be what you want. It assumes E1 is a cell offset and the value in F1 refers to the integers 4-16 inclusive in column A. -- Regards, Tom Ogilvy "acberry " wrote in message ... Hi I'm not even sure this is possible so advice would be welcome! I need to define a y range based on two variable x parameters, x1 and x2. To give an example, x values ranging as integers from 4 to 16 are in column A (A1:A13) on my spreadsheet, corresponding y values in column B. I have listed x1 in cell E1 and x2 in cell F1. x1 = 0 and x2 = 12 for this particular example. If 0<=x<12, I want to define the corresponding y range from column B. Here it would be the range B1:B8. Any suggestions? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Define variable range
Thanks for the suggestion Tom
In the end what I've done is this: First of all I named the data sheet OriginalData, and added a new blan sheet Calculations. I added data labels to the original data, so tha the data itself was in cells A2:A14 Public Sub FirstRange() Dim i As Integer Sheets("OriginalData").Activate Range("A2").Select Dim xvalues As String Dim yvalues As String Dim Rng1 As Range Dim Rng2 As Range Set Rng1 = Range("e1") Set Rng2 = Range("f1") i = 1 xvalues = Sheets("OriginalData").Range("A" & i).Value yvalues = Sheets("OriginalData").Range("B" & i).Value Do While ActiveCell.Value < Rng2 i = i + 1 xvalues = Sheets("OriginalData").Range("A" & i).Value yvalues = Sheets("OriginalData").Range("B" & i).Value 'Populate the sheet Calculations with selected range Sheets("Calculations").Activate Range("A65536").Select Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Select ActiveCell.Value = xvalues ActiveCell.Offset(0, 1).Value = yvalues Loop End Sub - bit clunky but it works! Tom Ogilvy wrote: *=Offset(B1,E1,0,F1-4,1) Might be what you want. It assumes E1 is a cell offset and the value in F1 refers to th integers 4-16 inclusive in column A. -- Regards, Tom Ogilvy "acberry " wrote in message ... Hi I'm not even sure this is possible so advice would be welcome! I need to define a y range based on two variable x parameters, x and x2. To give an example, x values ranging as integers from 4 to 16 ar in column A (A1:A13) on my spreadsheet, corresponding y values i column B. I have listed x1 in cell E1 and x2 in cell F1. x1 = 0 and x2 12 for this particular example. If 0<=x<12, I want to define the corresponding y range from colum B. Here it would be the range B1:B8. Any suggestions? --- Message posted from http://www.ExcelForum.com/ -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Define variable range input for SUM() function | Excel Worksheet Functions | |||
Define Variable Name using VBA | Excel Discussion (Misc queries) | |||
Using a Variable to Define Series | Charts and Charting in Excel | |||
How to take the worksheet name as a variable(Label/Define) in a fo | Excel Worksheet Functions | |||
Syntax to define a variable for current file | Excel Programming |