how to use offset with non-adjacent named range
 

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
remove the .nospam

how to use offset with non-adjacent named range
 

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
remove the .nospam

how to use offset with non-adjacent named range
 

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_cum = v_actual_cum + rst("ACTUAL").Value
pr_ExtractRange.Areas(2).Item(vi_row, 2).Value =
v_actual_cum
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

how to use offset with non-adjacent named range
 

Send requests for design changes to



--
Regards,
Tom Ogilvy


"Tig" wrote in message
oups.com...
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_cum = v_actual_cum + rst("ACTUAL").Value
pr_ExtractRange.Areas(2).Item(vi_row, 2).Value =
v_actual_cum
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

how to use offset with non-adjacent named range
 

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