Entering values into array variable column

[kingofaces]

I have an array variable that I'm looping through to calculate its values based on a row and column variable. I'm not having any issues with doing that for calculations, but one column I would like to include in the array is already in the excel spreadsheet. Could someone explain how I would refer to an entire column in an array and basically "paste" the entire column from excel into it?. I expect this would speed things up instead of needing to go through each cell in the column, retrieve it's value, and enter it into the array one value at a time through a loop. The example statement below should show what I'm trying to do:

ReDim Predict25Array(1 To Endrow, 1 To Endcolumn)

Predict25Array(Column3) = Worksheets("Sheet1").Range("G3:G103")

'loops for all other values in array

So the basic question is, is there a way to refer to the third column in the array like "Predict25Array(Column3)" and have it contain all the values that I am referring to in the worksheet range? Basically it should be the equivalent of copying one column in a worksheet and pasting it to another column in a worksheet, but instead it's being pasted into an array. It seems like there should be a cleaner way of doing that than using loops because of that. I just picked up on using arrays recently, so hopefully it's just something simple I haven't picked up yet that would make such a statement work.

 

[kingofaces]

A quick bump.

 

[kingofaces]

Bump.

 

[kingofaces]

I should point out that the size of the array is already set up to be exactly the same number of rows as the column that the data is being taken from. The Endrow variable takes care of that among other things I've left out, so the correct size shouldn't be an issue here.

 

[kingofaces]

bump

 

[GlennUK]

Why does it have to be part of larger array? Why don't you put the range Worksheets("Sheet1").Range("G3:G103") into it's own array ... that can be done in one step easily.
MyArray=Worksheets("Sheet1").Range("G3:G103").Value

 

[kingofaces]

Because eventually this is all going to be exported to notepad (and to SAS), so I would like to keep all the data in one file. Each column in this array is a different variable, and the column I'm trying to enter into the array currently is what each of the other variables will be compared to to with different statistical procs in SAS. I'm going to have something on the order of 500+ variables, so I'm putting the data into an array and then exporting to notepad since a single excel sheet can't hold all the data. It's pretty simple to just get that missing column into SAS seperately, but since other people will most likely be using the spreadsheet, I would much rather all of the output be in one file rather than needing to piece them together later on.

You did give me one idea though Glenn. If I put the range into it's own array, would it work to then just put that array into the larger array somehow? This is only a single step here, so I'm not expecting any big gains on execution time, but I do have similar sections of code that I loop through a couple hundred times that might benefit from something like this.

 

[GlennUK]

How are you populating the other columns of the array?

 

[kingofaces]

Just by looping with variables.

 Predict25Array(jRowob, Analysisrow1) = Predict25

Basically the loop enters all the values for the resulting column of the jRowob variable by looping through all the rows. It then moves on to the next column and loops through that, rinse and repeat. Each Predict25 value is calculated in VBA already (which is also related to the two variables designating the array size, so that part is fairly quick and clean. Retrieving that one column from excel though seems somewhat roundabout by doing something like this:

For i = 3 To 103
Predict25Array(3, i) = Worksheets("Sheet1").Range("G" & i).Value
Next i

Maybe it's just that arrays are inherently a bit clumsy in that manner, but it seems like there should be a more efficient way to populate an array. Maybe I'm wrong though since I'm just getting familiar with using them. Retrieving data from Excel seems to be what's slowing down the rest of macro, so I'm basically trying to look at ways I could speed things up since it seems like things move faster if that data only needs to be retrieved from an array versus from the actual excel spreadsheet. It's a lot to sort through, so I'm definitely not posting the entire macro, but figuring out what works best in this example would guide me towards what my next step should be here.

 

[GlennUK]

I totally agree that arrays are a bit clumsy ... I'd love to be able to move chunks around in single operations.

Anyway, how about doing the load from the spreadsheet first then filling in the rest of the array afterwards?

EndRow = 101
EndColumn = 4
ReDim Predict25Array(1 To EndRow, 1 To EndColumn)

Predict25Array = Worksheets("Sheet5").Range("E3:H103").Value
' then fill in around the area for column G.