J
John Keith
Can this be done?
....Create a 1d variant array from a range using an assignment directly to
the array like the 2d assignment statement?
'***test code***
Option Explicit
Option Base 1
Sub Array_Play()
Dim i As Integer, j As Integer, k As Integer
Dim vaData As Variant
'ThisWorkbook.Names.Add Name:="rngItemNo",
RefersTo:="=ProduceTotals!$A$6:$B$28" 'IS a 2d range
ThisWorkbook.Names.Add Name:="rngItemNo",
RefersTo:="=ProduceTotals!$A$6:$A$28" 'IS a 1d range
i = Range("rngItemNo").Rows.Count
j = Range("rngItemNo").Columns.Count
If j > 1 Then
'ReDim vaData(i, j) '<- handled automatically by the next stmt
vaData = Range("rngItemNo").Value
GoTo ArrayDone
End If
' still makes a 2d array... how do I code this to make vaData a 1d array
with out having to use the looping code below?
vaData = Range("rngItemNo").Value
Stop
' This loop loads the array as 1d, i'm looking for alternate ways to
accomplish this. Preferably a 1 line assigment statement.
ReDim vaData(i)
Dim c As Range
k = 0
For Each c In Range("rngItemNo").Cells
k = k + 1
vaData(k) = c.Value
Next c
ArrayDone:
Stop 'and look at the Locals
End Sub
....Create a 1d variant array from a range using an assignment directly to
the array like the 2d assignment statement?
'***test code***
Option Explicit
Option Base 1
Sub Array_Play()
Dim i As Integer, j As Integer, k As Integer
Dim vaData As Variant
'ThisWorkbook.Names.Add Name:="rngItemNo",
RefersTo:="=ProduceTotals!$A$6:$B$28" 'IS a 2d range
ThisWorkbook.Names.Add Name:="rngItemNo",
RefersTo:="=ProduceTotals!$A$6:$A$28" 'IS a 1d range
i = Range("rngItemNo").Rows.Count
j = Range("rngItemNo").Columns.Count
If j > 1 Then
'ReDim vaData(i, j) '<- handled automatically by the next stmt
vaData = Range("rngItemNo").Value
GoTo ArrayDone
End If
' still makes a 2d array... how do I code this to make vaData a 1d array
with out having to use the looping code below?
vaData = Range("rngItemNo").Value
Stop
' This loop loads the array as 1d, i'm looking for alternate ways to
accomplish this. Preferably a 1 line assigment statement.
ReDim vaData(i)
Dim c As Range
k = 0
For Each c In Range("rngItemNo").Cells
k = k + 1
vaData(k) = c.Value
Next c
ArrayDone:
Stop 'and look at the Locals
End Sub