Populating an array from sheet... efficiently?!

[BlueSkies]

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...
.....Worksheets("Reference").Select
.....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 = ActiveCell.Value
.......ActiveCell.Offset(1, 0).Select
.....Next
.....Worksheets("Reference").Visible = xlSheetHidden '...OR THIS

End Sub

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


Many thanks

 

[Colin Legg]

Hi,

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?

 

[BlueSkies]

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
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
___Worksheets("Reference").Select
___Range(strRefCell).Select

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

___Worksheets("Reference").Visible = xlSheetHidden

End Sub

Any other code you want I can provide!

Thanks,



Alex

 

[BlueSkies]

Any words of wisdom on improving this code?...

 

[pgc01]

Hi

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)
Next
End Sub

Remark:

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