Manipulating ranges using variables - a few questions pls

[Mightyginger]

A few questions about manipulating ranges.

1. Firstly if I have named a range and want to assign that range to a variable in a procedure in vba how do I do that? (I know this will be dead easy but can't seem to do it).
Say,
dim testrange as range
testrange=range("therangeishavealreadydefined&quot
it just gives me an error - how do I refer to to an already named range?

2. Imagine I have three columns of data. The first is a column with sequential dates (column A) and the other two columns have the prices for two different shares (so share1 in column B, share 2 in column C). The user then specifys dates between which they'd like to examine the difference between the two prices. At the moment I just loop through each row checking to see if the date is within the two dates specified and then take the difference of the cells. Is there a more efficient way to this this using ranges? I currenctly refer to each cell with the .cells notation using vairables

3. And just a quick one to finish. I want to assign some data to a range. If I know the column number it's in and row in which the data starts and the row when it ends can I assign a range to this set of data? And if so can I also name it using a variable?

Phew, thats all for now. many thanks in advance for any help you're able to provide.



Neil.

 

[Jooky68]

1. testrange=range("therangeishavealreadydefined&quot.value

....in order to do that you must have already named the range.

3. You can select a range bigger than just 1 cell

range("A5:C10&quot.select
'will select from row 5 column A all the way to row 10 in column C, also selecting any cells in the middle.

A range of more than one cell can also be named. Say you named that above group of cells, testrange ...

range("testrange&quot.select 'will select the group of cells

 

[Mightyginger]

thanks, but say I want to use variable in the range selection. So instead of range("A5:C10&quot.select I instead want to use variable to define the rows and columns? Can I do that?

e.g. range("columnirowj:columnkrowl&quot.select?

 

[Jooky68]

i know that you can use one variable in the range, like say you had columnrow="A5:C10" you can use it in the range(columnrow).value statment.

You can do this

range(cells(rowi,columni),cells(rowj,columnj)).select

 

[Jooky68]

also you can have....

range(variable1,variable2).select

but i think the above solution will solve your problem...just letting you know incase you didnt already.

 

[Zathras]

1. Use the "Set" command to assign an object. (A range is an object - look it up in the help.):
[blue]

[red]

Set

[/color]

 testrange=range("therangeishavealreadydefined")

[/color]
2. Depending on how much data you have, your way is probably just fine. If you have thousands of rows of data, and the dates are sorted, then it may be faster to use WorksheetFunction.VLOOKUP to find the dates.

3. Jooky68 had that one right.

 

[Jooky68]

sorry misunderstood the first question

 

[Mightyginger]

thanks guys for all your help.


Neil.