Reading a Range into a VBA Array

[Joe2111]

I'm tearing my hair out over what I am sure is a simple problem.

I want to read a series of Excel ranges into VBA arrays. I set up a simple test to read a 3C x 5R range into an array.

Sub testforms()

Dim GrowthArray As Variant

GrowthArray = Worksheets("Variables").Range("SalesGrowth")

For i = 1 To 3
For j = 1 To 5
MsgBox GrowthArray(j, i) 'to check that it worked
Next j
Next i

End Sub

This all seemed good so I moved on to what I thought was a simpler array with just 1 column x 5 rows


Sub testforms2()
Dim BusinessStreams As Variant
BusinessStreams = Worksheets("Variables").Range("BusStreams")
For i = 1 To 5
MsgBox BusinessStreams(i)
Next i
End Sub

This code seems to match the earlier version but when I run it I get a 'subscript out of range' error.

HELP!

 

[Andrew Poulsom]

Welcome to the Board.

For a 1D array use Transpose:

BusinessStreams = WorksheetFunction.Transpose(Worksheets("Variables").Range("BusStreams").Value)

 

[Peter_SSs]

Joe

Welcome to the MrExcel board!

You will still have a 2-D array. Try changing your message box line to:

MsgBox BusinessStreams(i, 1)

 

[Joe2111]

Wow - that was quick!

Both solutions worked beautifully but the (i,1) seems simpler.

Thanks for saving my sanity - but I expect I'll be back.

 

[Joe2111]

Now I've got a follow up question.

I've used the range to array code to populate a user form with all of the variables that I want the user to be able to play with. After they've made all the changes I wan't the new values to be written back onto the spreadsheet.

To move the range to the array I used the line

BusStreams = Worksheets("Variables").Range("BusStreams")

Where the first BusStreams is the VBA array and the second "BusStreams" is the named range on the spreadsheet.

I'm now looking for the reverse that will switch the flow around. Something like:

Worksheets("Variables").Range("BusStreams") = BusStreams

Is it as simple as that? Or will I need a routine to read the user form fields one by one and populate the range?

Cheers

 

[Peter_SSs]

No, you don't need to loop through all the values. It depends a bit on what you already know about the size of your arrays, but this might help.

With this data:

Excel Workbook
	A	B	C
1	a	1
2	b	2
3	c	3
4	d	4
5	e	5
6
Range to Array to Range

Try this code:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> testArrays()<br>    <SPAN style="color:#00007F">Dim</SPAN> myArray<br>    <br>    myArray = Range("A1").CurrentRegion.Value<br>    Range("E4").Resize(UBound(myArray, 1), UBound(myArray, 2)).Value = myArray<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Joe2111]

At the risk of sounding a bit thick, I'm not sure if I follow.

I've used my code to transfer the values from a range on the spreadsheet into an array. I then use the array to populate the fields on a user form. After the user has edited these fields I will transfer all those fields (including both changed and unchanged) back into the arrays. So I now have an array (identical in size etc., to the range) that I'd like to drop back into the range. Hopefully that means I won't have to worry about the sizes etc., and I was hoping that there was a process that would let me 'paste' the array with the new values back onto the original range.

Thanks again.

 

[Richard Schollar]

What you had in your previous message ie:

Worksheets("Variables").Range("BusStreams") = BusStreams

Should work.