Handling Multiple Calculations and Referring to a New Range, but All Inside of VBA

[LineOfBestFit]

Hi All,

Dumb question for the master programmers here. Suppose I have a range of 100 numbers (or whatever number, could vary) that I want to perform some calculations on. The original range lives in the worksheet. I then want to create another range that is the result of these calculations, but I want the new range to "live" in the VBA, and not appear in the worksheet. How do I refer to the new range and "handle" it within VBA?

For instance, suppose the original range is 100 numbers. Now let's say I wanted to create a new range as a result of squaring the original range, and then I wanted to find the slope between the worksheet range and this new VBA range using Application.WorksheetFunction.SLOPE (this could be anything, I'm just using a simple example). I'm completely lost as to how to deal with calculating and storing the new range, then referring to it, all within the code.

Thanks so much for your help,

LBF

[Norie]

Perhaps something like this.

Please Login or Register  to view this content.

[LineOfBestFit]

Norie,

I only had 15 minutes to play around with what you gave me, but I have to say, this little snippet of code is a lifesaver and I wanted to post back ASAP and tell you so (I need to research this "Evaluate" technique! Awesome.). I'm going to modify this tomorrow; basically I need to build a function within a larger collection of code but this is a critical building block for me.

I hope you don't mind if I reply back again tomorrow -- I may have some other questions (for instance, how could you end up manipulating "arry" to something more complicated, such as having it be a cumulative total or something), but I can't test it further tonight before bothering you again.

Again, many, many thanks!