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