Populating an array from sheet... efficiently?!


New Member
Mar 24, 2009
Dear guys and girls,

I have an Array to be populated with intArray (an integer variable set earlier) number of values from a column in a sheet called "Reference". The source column varies, but is always the column holding the cell, "strRefCell".

I would like to avoid having to unhide the Reference sheet (necessary for the code below to work), select the sheet, locate the first cell to copy from (two cells above strRefCell) before finally using a For/Next loop to populate the values of the array as I suspect there might be an easier way.

Is there a way I can perform this in a more streamlined way?

To give you an idea of what I had in mind, my (fawlty) code attempt was:

arrWithForm = Worksheets("Reference").Range((strRefCell.offset(-2,0)):(strRefCell.offset((intArray-2),0))

Here's what is doing the job. I have other more complex Subs I'd like to apply a successful alternative logic to (including copying values from the array into the sheet), otherwise I wouldn't ask for your help for such a small thing! :)

Sub RefToArray()

.....Dim n As Integer

.....Worksheets("Reference").Visible = True 'WON'T WORK WITHOUT THIS...
.....Range(strRefCell).Offset(-2, 0).Select 'finds first cell to pull _
.....data from by using strRefCell (Row 4) and offsetting 2 rows up.

.....For n = 1 To (intArray + 2)
.......arrWithForm(n) = ActiveCell.Value
.......ActiveCell.Offset(1, 0).Select
.....Worksheets("Reference").Visible = xlSheetHidden '...OR THIS

End Sub

.....Public arrWithForm() as Currency
.....Public strRefCell as String
.....Public intArray as Integer

Many thanks

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Yes you can significantly improve the current process, but what do you plan to do with the array once you have loaded the data into it?
Upvote 0
Hi Colin,

That's good news indeed. The outline is that the Budget.xls contains three sheets for Household account, and two personal accounts belonging to my partner and I. There is a fourth sheet, "Reference", for calculations and bits and bobs, which is hidden.

At present the basic system is that one master macro, "Piggy", determines which sheet you're looking at and sets intArray (used immediately afterward to ReDim the size of the two arrays), sets the variables strThisSheet and thisWS, a Worksheet Object, before calling PopulateForm which houses Calls for several other routines:

1) StartingCell 'uses Ref sheet to set strBudgetCell (to extract data) and strRefCell (to paste data)
2) ExtractData 'copies data from Budget to an Array - arrExtracted(n)
3) DataToRef 'pastes this data into Ref sheet
4) RefToArray 'copies values from Ref (now including Totals) to another Array - arrWithForm
5) ArrayToForm 'copies values from arrWithForm into form
6) Call PrepareForm 'prepares form depending on ThisWS

...the form then shows and Piggy's job is done.

The reason for two arrays (I'm thinking unnecessary!) rather than just one, was because the Reference sheet contains two extra rows for keeping a running total of income and expenditure during userform manipulation - hence arrWithForm is (intArray + 2)

Here is the code for DataToRef, to give you an idea of the other code I've overwritten!

Sub DataToRef()

___Dim n As Integer

___Worksheets("Reference").Visible = True

___For n = 1 To intArray
_____Select Case n
________Case Is < 4
__________ActiveCell.Value = arrExtracted(n)
__________ActiveCell.Offset(1, 0).Select
________Case Else
__________ActiveCell.Value = arrExtracted(n) * -1 'these are expenditures and are made positive as they go into the Reference sheet
__________ActiveCell.Offset(1, 0).Select
_____End Select

___Worksheets("Reference").Visible = xlSheetHidden

End Sub

Any other code you want I can provide!


Upvote 0

If I understood correctly this is equivalent to the sub you posted:

Sub DataToRef()
Dim rRef As Range
Dim n As Integer
Set rRef = Worksheets("Reference").Range(strRefCell)
For n = 1 To intArray
    rRef.Offset(n - 1) = arrExtracted(n) * IIf(n < 4, 1, -1)
End Sub


You forgot to post the definition and initialisation of intArray, strRefCell and arrExtracted(). I assumed they were dealt with somewhere else.
Upvote 0

Forum statistics

Latest member

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