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

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/

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