Change Rows/Columns in range???
 

Thanks Tom! And you too Pete! Got it working, and it works great!! :)


"Tom Ogilvy" wrote in message
...
yes

set rng = Range("A10:A11")
i = 1
rng.Resize(rng.rows.count+i,1) will be A10:A12

as a short illustration. With a blank sheet active

Sub Tester1()
Dim rng As Range
Dim rw As Long
Dim i As Long, j As Long
Cells(1,1) = "i"
cells(1,2) = "j"
cells(1,3) = "range"
rw = 2
Set rng = Range("A10:A11")
For i = 0 To 5
For j = 1 To 3
Cells(rw, 1).Value = i
Cells(rw, 2).Value = j
Cells(rw, 3).Value = rng.Offset(0, j - 1) _
.Resize(rng.Rows.Count + i, 1).Address
rw = rw + 1
Next
Next

End Sub

--
Regards,
Tom Ogilvy


"43fan" wrote in message
...
Tom,

No problem. I guess I should've explained that I'm virtually a complete
beginner at this. I guess not "complete", but pretty close. ;)

Anyway, maybe if I understood what is happening, I could tailor this to
my
needs easier. The resize command, is this what it's doing? If the range
initially is "A10:A11", when it resizes it(and i = 1), is the range now
"A10:A12"?

Also, in looking more at how the code is written, I'm moving across
columns
in one loop(the inside loop) and down rows in the other(outside loop).
So
I'm thinking that in the inside loop I have to change the column
setting,
in
the outside loop is where I need to add to the rows in the range.
Unfortunately, within the inside loop is where I "print" the data. I
wish
I
could explain this better, sorry. :(


"Tom Ogilvy" wrote in message
...
My fault for leaving out the count part - apologies
rng1 and rng2 will never change

you can see what they refer to with address

set rng1 = rng1.resize(rng1.rows.count + i, 1)

will not work for what you originally described because it redefines
rng1
on
each loop
dim rng1 as Range
Dim rng2 as Range
dim i as long
Dim var as Variant

set rng1 = Range("B13:B14")
set rng2 = Range("A13:A14")



for i = 0 to 10
var = application.Forecast(0,rng1.resize(rng1.rows.count + i,1), _
rng2.resize(rng2.rows.count + i ,1))
msgbox rng1.resize(rng1.rows.count + i, 1).Address & " " & _
rng2.resize(rng2.rows.count + i, 1).Address
Next i


you want to start with zero so you don't change the ranges before you
do
your first forcast.

if you want to change columns, then you will have to introduce an
offset -
it is unclear why/when you would change columns.

--
Regards,
Tom Ogilvy


"43fan" wrote in message
...
Pete,

Thanks, I think the first one will work, I just need to adjust the
starting
range to accomodate it.

How can I then see(in debug mode) the values of rng1 and rng2? In
order
to
know if they're referencing the proper cells or not? I've tried
putting
my
mouse cursor over them and even tried "printing" them in the
immediate
window, neither works. The mouse gets nothing, and the "print"
results
in
a
type mismatch error.

Thanks!
Shawn

"Pete McCosh" wrote in message
...
Shawn,

try

set rng1 = rng1.resize(rng1.rows.count + i, 1)

Although this will probably have the effect of increasing
by one row on the first pass, two on the second and so on,
so maybe:
set rng1 = rng1.resize(rng1.rows.count + 1, 1)

would be better?

Cheers, Pete

-----Original Message-----
I'm running the Forecast() function, which uses ranges in
order to process.
I initialize two variables, rng1 and rng2 to type range,
then set them equal
to the starting range I want.

I'm running it in a nested loop, based on (a) - how many
data sets and (b)
how many points of data within each set. I need to
increase the range with
each data set, as well as change the column it's looking
at for each data
point.

Say I'm starting with this:
rng1 = "B13:B14"
rng2 = "A13:A14"

Running var1=application.Forecast(0,rng2,rng1) works
fine, but of course I
get the same value for all data points/all data sets. I
can't figure out
how to get the ranges to resize, using rng1.Resize
(rng1.rows + i,1)
gives me a type mismatch error.

Help???!!!

Thanks!
Shawn


.