Initialising array directly from range name

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
I have some VBA that requires an array to be initialised. It is a simple 8-element string array, which is working fine when populated in the code as follows:
Code:
ArrayIn = Array("A", "B", "C", "D", "E", "F", "G", "H")
I would like to have this populated from a named range, thereby allowing the user to modify the array values without changing the code.
I am sure this must be possible, and I realise I could do it by looping through the range data, but is there a neater way of achieving this.
I tried:
Code:
ArrayIn = Array(Range("ArrayValues"))
but this is not working

Any ideas?

Thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi, Do you mean like this:-
If you named Cell has your Letters Seperated by Commas, then this will dispay the array in row 10.

Code:
[COLOR="Navy"]Sub[/COLOR] MG27Jul47
[COLOR="Navy"]Dim[/COLOR] oLets [COLOR="Navy"]As[/COLOR] Variant, L [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
oLets = Split(Range("MyRng"), ",")
    
    [COLOR="Navy"]For[/COLOR] L = 0 To UBound(oLets)
        Cells(10, L + 1) = oLets(L)
    [COLOR="Navy"]Next[/COLOR] L
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Mick,

Thanks for the reply.
I already have the array in separate cells, what I am trying to do is to allow the array in VBA to be populated directly from the range in the spreadsheet.

Using your example, we end up with the individual array elements in cells A10:J10. I want the VBA code to declare a 10-element array, and then fill the array elements with the contents of cells A10:J10 (which I would give a name to)

Steve
 
Upvote 0
Hi, Perhaps like this:-
Code:
Dim MyArray
MyArray = Range("MyRange") ' "MyRange" = Range("A1:J10")

'Check array by Placing data back On sheet
Range("A12").Resize(, UBound(MyArray, 2)) = MyArray
'or
Range("A14").Resize(UBound(MyArray, 2)) = Application.Transpose(MyArray)
Regards Mick
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top