Writing Array To A Named Range

[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

 

[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

 

[Dave Peterson]

I'd try:

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

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

 

[IanC]

Dave,

This works perfectly. Thank you very much.


IanC

 

[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

 

[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