how to use offset with non-adjacent named range

[Tig]

Hi all.

I have used Offset with a Named Range before to loop through a named
range, setting the values. However when i try this with a named range
that has non-adjacent columns, I can't get it to work. Here is the
simplest scenario.

If I have a named range made up of the following cells - A1:A9 and
C1:C9, both of the following lines returns the value in A1:

Range("Data").Item(1, 1).Value
Range("Data").Cells(1, 1).Value

If I want to refer to the first row but the second column in the named
range, i would expect either of the following to work:

Range("Data").Item(1, 2).Value
Range("Data").Cells(1, 2).Value

However, both of these return me the value in cell B1 !! Why? Why not
C1? Cell B1 is not even in my named range. Why is column 2 the column
physically to the right of column 1 in the range, rather then being the
second column in my named range?

Can anyone show me how to get to the second column in my named range,
without having to hardcode the number of columns to jump over?
Obviously, i know that

Range("Data").Item(1, 3).Value

will give me the value in cell C1 but that kind of invalidates the
whole point of using a named range - I shouldn't need to know the
structure of the sheet.

Thanks in advance,
Simon Cullen
[email protected] remove the .nospam

[keepITcool]

Tig,

A range with non-adjacent cells is called a multiarea range.

working with multareas ranges you have to beware of many aspects:
Rows and Columns only apply to the first area...

And (ofcourse..)
you cannot use offset or cells in the manner you are used to.

First look at areas property of the Range object in VBA help.

then you'll find that
Range("data").Areas(2).Cells(1) will yield you C1



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tig wrote :

> Hi all.
>
> I have used Offset with a Named Range before to loop through a named
> range, setting the values. However when i try this with a named range
> that has non-adjacent columns, I can't get it to work. Here is the
> simplest scenario.
>
> If I have a named range made up of the following cells - A1:A9 and
> C1:C9, both of the following lines returns the value in A1:
>
> Range("Data").Item(1, 1).Value
> Range("Data").Cells(1, 1).Value
>
> If I want to refer to the first row but the second column in the named
> range, i would expect either of the following to work:
>
> Range("Data").Item(1, 2).Value
> Range("Data").Cells(1, 2).Value
>
> However, both of these return me the value in cell B1 !! Why? Why
> not C1? Cell B1 is not even in my named range. Why is column 2 the
> column physically to the right of column 1 in the range, rather then
> being the second column in my named range?
>
> Can anyone show me how to get to the second column in my named range,
> without having to hardcode the number of columns to jump over?
> Obviously, i know that
>
> Range("Data").Item(1, 3).Value
>
> will give me the value in cell C1 but that kind of invalidates the
> whole point of using a named range - I shouldn't need to know the
> structure of the sheet.
>
> Thanks in advance,
> Simon Cullen
> [email protected] remove the .nospam

[Tig]

Thank you Mr Cool, the Areas property did indeed solve my problem. It
all works now.

I do think that it is pretty clunky however. In my case with only 2
areas and 3 columns I could get around it and still move through the
areas in a loop without referring to the sheet structure, but if my
Named Range was any more complex it would not have been possible.

This is (some of) mine:

rst.MoveFirst
Do While Not rst.EOF
vi_col = 1
For Each fld In rst.Fields
pr_ExtractRange.Areas(vi_col).Item(vi_row, 1).Value =
fld.Value
vi_col = vi_col + 1
Next fld

If rst("Day") <= Now() Then
v_actual_*** = v_actual_*** + rst("ACTUAL").Value
pr_ExtractRange.Areas(2).Item(vi_row, 2).Value =
v_actual_***
End If

vi_row = vi_row + 1
rst.MoveNext
Loop

You can see I have hard-coded an area number and some column numbers in
there. So, as I say, it works for me, as I have this simple named
range identically set up on all of my sheets, with the same local named
range on every sheet. On a more complex scenario though, where you
genuinely want to loop through an unknown number of columns for an
unknown shape of named range, it wouldn't appear to cut it.

Thanks for your help!
Simon Cullen
Australia

[Tom Ogilvy]

Send requests for design changes to

[email protected]

--
Regards,
Tom Ogilvy


"Tig" <[email protected]> wrote in message
news:[email protected]...
> Thank you Mr Cool, the Areas property did indeed solve my problem. It
> all works now.
>
> I do think that it is pretty clunky however. In my case with only 2
> areas and 3 columns I could get around it and still move through the
> areas in a loop without referring to the sheet structure, but if my
> Named Range was any more complex it would not have been possible.
>
> This is (some of) mine:
>
> rst.MoveFirst
> Do While Not rst.EOF
> vi_col = 1
> For Each fld In rst.Fields
> pr_ExtractRange.Areas(vi_col).Item(vi_row, 1).Value =
> fld.Value
> vi_col = vi_col + 1
> Next fld
>
> If rst("Day") <= Now() Then
> v_actual_*** = v_actual_*** + rst("ACTUAL").Value
> pr_ExtractRange.Areas(2).Item(vi_row, 2).Value =
> v_actual_***
> End If
>
> vi_row = vi_row + 1
> rst.MoveNext
> Loop
>
> You can see I have hard-coded an area number and some column numbers in
> there. So, as I say, it works for me, as I have this simple named
> range identically set up on all of my sheets, with the same local named
> range on every sheet. On a more complex scenario though, where you
> genuinely want to loop through an unknown number of columns for an
> unknown shape of named range, it wouldn't appear to cut it.
>
> Thanks for your help!
> Simon Cullen
> Australia
>

[Tig]

Thanks Tom, I have put in my wish list. Not sure that I will be around
to see it happen but hopefully some one will benfit from it.
smc