Help with Arrays & Ranges in VBA
 

Hi and thanks for checking out my thread, hopefully you can help.
I have a procedure that calls another procedure, the most importan
argument that I pass in is a range (i.e. Yrange as Range). In Proc
(first procedure) I read in this range from excel but after this, i
where I cannot seem to do what I'd like...I have to manipulate th
elements of this range, for example a new range I'd like to create i
the Log of this range and pass that in As a Range to Proc2. If
cannot manipulate ranges with much freedom (one other example is I'
like to create a new range that is made up of the differences of th
original range) I don't mind assigning the range to an array an
manipulating that but how can I turn this array back into a Range (I'v
had to do this in Proc2 and it is very inelegant, involves pasting to
sheet then reading back in As a Range!). I should note here that
have found ways around this problem but they are all very pedestrian s
I'm hoping someone can provide an efficient and elegant solution fo
converting Ranges to arrays then back to a range or just a way t
manipulate a range itself and calling the result a new range! Thank
in advance!!
PS one other point is that it would certainly might be better to forge
ranges all together and do all this with arrays, that'd be Ok with m
but in the end (in Proc2) I need to call on Excel's multiple regressio
function and it requires Ranges to be passed in so perhaps someon
knows how to get around that with Arrays

--
Message posted from http://www.ExcelForum.com

Help with Arrays & Ranges in VBA
 

Dim var as Variant
Dim sz1 as long, sz2 as Long
Dim i as long, j as Long
var = Range("A1:B20").Value
sz1 = Ubound(varr,1) - Lbound(varr,1) + 1
sz2 = Ubound(varr,2) - Lbound(varr,2) + 1
for i = 1 to sz1
for j= 1 to sz2
var(i,j) = log(var(i,j))
next j
next i
Range("F10").Resize(sz1,sz2).Value = var

--
Regards,
Tom Ogilvy

"CanQuant " wrote in message
...
Hi and thanks for checking out my thread, hopefully you can help.
I have a procedure that calls another procedure, the most important
argument that I pass in is a range (i.e. Yrange as Range). In Proc1
(first procedure) I read in this range from excel but after this, is
where I cannot seem to do what I'd like...I have to manipulate the
elements of this range, for example a new range I'd like to create is
the Log of this range and pass that in As a Range to Proc2. If I
cannot manipulate ranges with much freedom (one other example is I'd
like to create a new range that is made up of the differences of the
original range) I don't mind assigning the range to an array and
manipulating that but how can I turn this array back into a Range (I've
had to do this in Proc2 and it is very inelegant, involves pasting to a
sheet then reading back in As a Range!). I should note here that I
have found ways around this problem but they are all very pedestrian so
I'm hoping someone can provide an efficient and elegant solution for
converting Ranges to arrays then back to a range or just a way to
manipulate a range itself and calling the result a new range! Thanks
in advance!!
PS one other point is that it would certainly might be better to forget
ranges all together and do all this with arrays, that'd be Ok with me
but in the end (in Proc2) I need to call on Excel's multiple regression
function and it requires Ranges to be passed in so perhaps someone
knows how to get around that with Arrays!


---
Message posted from http://www.ExcelForum.com/