ReDim Variant array for worksheet copy
 

I am trying to create a variant array from multiple data sources then plop
that array into a worksheet.
Dim vaCounts() as Variant
ReDim vaCounts(1 To 1, 1 To 3) As Variant
For lRow = 1 To UBound(vaPOData, 1)
If vaPOData(lRow, 7) sLastSite Then
' process row that starts a site group
iSiteCount = iSiteCount + 1
If iSiteCount 1 Then ReDim Preserve vaCounts(iSiteCount, 3) As
Variant

I get subscript outof range when iSiteCount = 2
I dont understand why a ReDim would fail, the help says a ReDim can resize
the array as many times as you wish.
Is there a better way to create a variant array that will allow writing to a
worksheet?

If i knew I had 100 sites I could run this:
Dim vaCounts() as Variant
vaCounts = Range("A1:C100").value
.... make any changes to vaCounts row by row...
Range("A1:C100").value = vaCounts

How do I do this with out knowing that I need 100 rows to start with?

--
Regards,
John

ReDim Variant array for worksheet copy
 

You can only ReDim the last dimension of an array.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John Keith" wrote in
message
...
I am trying to create a variant array from multiple data sources
then plop
that array into a worksheet.
Dim vaCounts() as Variant
ReDim vaCounts(1 To 1, 1 To 3) As Variant
For lRow = 1 To UBound(vaPOData, 1)
If vaPOData(lRow, 7) sLastSite Then
' process row that starts a site group
iSiteCount = iSiteCount + 1
If iSiteCount 1 Then ReDim Preserve
vaCounts(iSiteCount, 3) As
Variant

I get subscript outof range when iSiteCount = 2
I dont understand why a ReDim would fail, the help says a ReDim
can resize
the array as many times as you wish.
Is there a better way to create a variant array that will allow
writing to a
worksheet?

If i knew I had 100 sites I could run this:
Dim vaCounts() as Variant
vaCounts = Range("A1:C100").value
... make any changes to vaCounts row by row...
Range("A1:C100").value = vaCounts

How do I do this with out knowing that I need 100 rows to start
with?

--
Regards,
John