On Apr 11, 6:02 pm, "NickHK" wrote:
Dean,
If you are getting the values from a range, you can do it in a single call.
Note that you will always get a 2 dimensional array though, even if you only
have a single row or column.

Private Sub CommandButton2_Click()
Dim RangeValues As Variant
Dim i As Long
Dim j As Long

RangeValues = Range("A1:A3")

For i = LBound(RangeValues, 1) To UBound(RangeValues, 1)
For j = LBound(RangeValues, 2) To UBound(RangeValues, 2)
Debug.Print i & "," & j & " : " & RangeValues(i, j)
Next
Next

End Sub

NickHK

wrote in message

oups.com...



On Apr 11, 3:47 pm, "NickHK" wrote:
Dean,
Adjust for your required data type:

Public Function SomeFunction(ArrayCount As Long)
Dim MyArray() As Long

ReDim MyArray(1 To ArrayCount)
'etc...

End Function

You will probably need to use LBound/Ubound also. May looking "Option
Base"
also.

NickHK

wrote in message

roups.com...

I want to allow the user to type in a list of cost centres and have
the code create an array from that list. The list may 1 record or it
may be 100. How do I create a dynamic array?- Hide quoted text -

- Show quoted text -

Hi Nick Thanks for the help! How do I fill it after I have redim'd
the array? I have created the code below that correctly returns the
correct redim count of 5. How do I fill only the 5 values from the
range?

Function ArrayDimension()
Dim rng As Range
Dim MyArray() As Long

Set rng = Worksheets("TEST").Range("A2:A100")
ArrayCount = Excel.WorksheetFunction.CountA(rng)

ReDim MyArray(1 To ArrayCount)

MyArray = rng.Value

Debug.Print MyArray

I keep getting a type mismatch.

Please advise.

Thanks- Hide quoted text -

- Show quoted text -

Ahh, that's great. Thanks Nick! I was having difficulty
understanding the UBound and LBound in this case. Now it is clear!

Cheers!