Change Rows/Columns in range???

[43fan]

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

 

[Pete McCosh]

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

 

[43fan]

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

 

[Tom Ogilvy]

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.

 

[43fan]

Also, I need to change the columns for the data that I'm using. IOW, for
the first set of data, I may be using A13:A14, second set of data then is
D1314. Resizing I'm sure isn't the right way to change the column, but I
don't know what is then.

Thanks!
Shawn
PS - Thanks, I think the resizing will work fine for the rows, just need to
figure out the column part.

 

[Tom Ogilvy]

use offset



--
Regards,
Tom Ogilvy

Also, I need to change the columns for the data that I'm using. IOW, for
the first set of data, I may be using A13:A14, second set of data then is
D1314. Resizing I'm sure isn't the right way to change the column, but I
don't know what is then.

Thanks!
Shawn
PS - Thanks, I think the resizing will work fine for the rows, just need to
figure out the column part.

 

[43fan]

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]

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

 

[43fan]

Got it, thanks!

use offset

 

[43fan]

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