Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / April 2008

Tip: Looking for answers? Try searching our database.

Writing Array To A Named Range

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
IanC - 14 Apr 2008 15:56 GMT
I am trying to manipulate a named range ("CompetitorShareOption") of 1 column
x 6 rows in VBA.  I have declared the array (Dim CompetitorOptionsArray as
Range), and am bringing it into VBA with the line:

Set CompetitorOptionsArray = Range("CompetitorShareOption")

However, when I try to write the back again to the named range with the line:

Range("CompetitorShareOption").Value = CompetitorOptionsArray

the named range becomes blank, despite there being values in the array.  I
get the same thing happen if I define the range by their cell labels.

Thanks in advance,

IanC
Charlie - 14 Apr 2008 16:54 GMT
You didn't declare your variable as an array, you declared it as a Range.  Is
this what you wanted to do?

Dim CompetitorOptionsArray As Variant

CompetitorOptionsArray = Range("CompetitorShareOption")

CompetitorOptionsArray(1, 1) = "First Cell"
CompetitorOptionsArray(2, 1) = "Second Cell"
etc.

Range("CompetitorShareOption") = CompetitorOptionsArray

> I am trying to manipulate a named range ("CompetitorShareOption") of 1 column
>  x 6 rows in VBA.  I have declared the array (Dim CompetitorOptionsArray as
[quoted text clipped - 12 lines]
>
> IanC
Dave Peterson - 14 Apr 2008 16:59 GMT
I'd try:

Range("CompetitorShareOption").Value = CompetitorOptionsArray.Value

But CompetitorOptionsArray is really a range--not an array.

> I am trying to manipulate a named range ("CompetitorShareOption") of 1 column
>  x 6 rows in VBA.  I have declared the array (Dim CompetitorOptionsArray as
[quoted text clipped - 12 lines]
>
> IanC

Signature

Dave Peterson

IanC - 14 Apr 2008 18:26 GMT
Dave,

This works perfectly.  Thank you very much.

IanC

> I'd try:
>
[quoted text clipped - 18 lines]
> >
> > IanC
Dave Peterson - 14 Apr 2008 21:00 GMT
It may work ok, but the code really doesn't do anything.  (Well, the snippet of
code you posted doesn't look like it's doing much.)

It's using range objects--so changing any value should be changing what you see
in the worksheet.  So you shouldn't have to plop the values back into the
worksheet.

Building on Charlie's suggestion -- and assuming that you really wanted to
manipulate the data in an array -- not in the range:

Option Explicit
Sub testme02()

   Dim CompetitorOptionsArray As Variant

   'this creates a 2 dimensional array (#rows x #columns)
   '6 rows x 1 column in your case
   CompetitorOptionsArray = Range("CompetitorShareOption").Value
   
   'manipulate the array as much as you want    
   CompetitorOptionsArray(1, 1) = "First Cell"  'row 1, column 1
   CompetitorOptionsArray(2, 1) = "Second Cell" 'row 2, column 1
   
   'then I'd use something like this to plop the array back into the worksheet
   Range("CompetitorShareOption") _
       .Resize(UBound(CompetitorOptionsArray, 1) _
                      - LBound(CompetitorOptionsArray, 1) + 1, _
               UBound(CompetitorOptionsArray, 2) _
                      - LBound(CompetitorOptionsArray, 2) + 1).Value _
           = CompetitorOptionsArray
   
End Sub

> Dave,
>
[quoted text clipped - 28 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

IanC - 15 Apr 2008 07:34 GMT
There was indeed other code in between, which did a simple manipulation of
the data in the range, but kept its size unchanged.

However, it looks as if yours and Charlie's suggestion takes this one stage
futher in that it allows the size of the named ranged to manipulated
depending on what happens in the array, which could be a very powerful
feature.

Thanks to both of you for these solutions.

IanC

> It may work ok, but the code really doesn't do anything.  (Well, the snippet of
> code you posted doesn't look like it's doing much.)
[quoted text clipped - 61 lines]
> > >
> > > Dave Peterson
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.