Define variable range

[acberry]

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

 

[Tom Ogilvy]

=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.

 

[acberry]

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!