Writing Array To A Named Range

I

IanC

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
 
C

Charlie

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
 
D

Dave Peterson

I'd try:

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

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

Dave Peterson

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
 
I

IanC

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top