Help with Arrays & Ranges in VBA


C

CanQuant

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
 
Ad

Advertisements

T

Tom Ogilvy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Loop / Array / Ranges 1
Range or Array? 12
ranges as arrays 2
How Populating Arrays/Range in VBA code 3
How to copy VBA array into range of cells? 4
VBA arrays 3
Array in VBA 10
Read a range to an array 4

Top