How to Loop through columns in an Array and write to a range

[LucasLondon]

Hi,

I have a very basic question on writing results from an input Array to an output location..

Currently I have the macro below which reads data into an array from sheet2 and outputs the first column of the array into sheet3:

[VBA]
Sub Array_example()
Dim Rawdata
Rawdata = Worksheets("Sheet1").Range("a1:d200")
Worksheets("Sheet3").Range("a5:A205").Value = Rawdata
End Sub
[/VBA]

I would like to amend the code so that it outputs each column of the input array interatively into the same place: ie ("Sheet3").Range("a5:A205").

So I want to do the first column (a1:a200) first, then the second column (b1:b200) and then the third etc.

I know there is various ways of doing this but what would the simpliest be without re-writing/making fewest changes to the existing code? I'm assuming I just need tp add some kind of "for each i" in array statement?

Thanks,

Lucas

[Bob Phillips]

[VBA]Sub Array_example()
Dim Rawdata
Rawdata = Worksheets("Sheet1").Range("a1:d200")
Worksheets("Sheet3").Range("a5").Resize(UBound(Rawdata, 1) - LBound(Rawdata, 1) + 1, UBound(Rawdata, 2) - LBound(Rawdata, 2) + 1).Value = Rawdata
End Sub
[/VBA]

[GTO]

...outputs each column of the input array interatively into the same place: ie ("Sheet3").Range("a5:A205").

So I want to do the first column (a1:a200) first, then the second column (b1:b200) and then the third etc....

Hi Lucas,

I took your question a bit differently then Bob did. I am not sure what "interatively" means though?

If (a BIG if at this point) I am understanding at all, you may want the individual columns (or more acurately, the values therein) plunked into different places. Where? Into the first column of the 'following' sheets?

Mark

[LucasLondon]

Hi,

Thanks Xld for the code.

Indeed by interatively I meant more in line with your understanding Mark than of Bob's whose code I have tested. It places the values of all columns of the Rawdata into sheet3 Range("A519").
Whereas I wanted to place the values of each column of the array one by one into only column A of sheet3.

This is to help improve my understanding of how to better work with arrays to programe things I need to do for different VBA routines.

Thanks,

Lucas

[Bob Phillips]

So do you mean, first column in A5:A205, second column in A206:A406, etc.

BTW, did you see my response to your Correlations question?

[LucasLondon]

Hi Bob,

Sorry what I meant was the first column in A5:A205, and then the next column in the same place, i.e overwrite (A5:A205) with each successive column!

I've just looked at the correlation analysis based on different moving averages - thanks for the updated code. I will test out over the weekend and post my feedback.

Cheers,

L

[Bob Phillips]

Can I ask what is the point of that? Why not just load the last column in - cut-out the middle-man?

[LucasLondon]

Ar, well I have a couple of routines that performs some complex calculations and comparisons on a given column and then makes a decision whether to add or exclude that data/column into a model.

So in the case above, each column of data would be imported into A5:A205 and then some analysis would be done on the data and a decision made. And then it would do the next variable. But my post was illustrative only, right now I just want to understand how I would go about doing the processing part.

I know how to do it using copying and paste and for i statement commands but I want to do it using arrays instead to help develop my understanding further.

Lucas

[Bob Phillips]

[VBA]Sub Array_example()
Dim Rawdata As Variant
Dim numRows As Long
Dim i As Long

Rawdata = Worksheets("Sheet1").Range("a1:d200")
numRows = UBound(Rawdata, 1) - LBound(Rawdata, 1) + 1
For i = LBound(Rawdata, 2) To UBound(Rawdata, 2)

Worksheets("Sheet3").Range("A5").Resize(numRows).Value = Application.Transpose(Application.Transpose(Application.Index(Rawdata, 0, i)))
'do your stuff
Next i
End Sub[/VBA]

[LucasLondon]

Thanks XLD.

This works as expected.

Bit confused about some elements/mechanics of how the code actually works as it seems a lot more complex than I thought it would be!

Without actually know much about it I thought naively that it would look something like:
[VBA]

Sub Array_Mockup()
Dim Rawdata
Rawdata = Worksheets("Sheet1").Range("a1:d200")
For i = 1 To 4
Worksheets("Sheet3").Range("a5:A205").Value = Rawdata.Range(Cells(1, i), Cells(200, i)).Value
Next i
End Sub
[/VBA]
And in terms of the code I'm not sure that I understand how it working out the number of rows just via the follwoing line as there does not appear to be any count function etc?

numRows = UBound(Rawdata, 1) - LBound(Rawdata, 1) + 1

And not sure why any data needs to be transposed in the following line?

Worksheets("Sheet3").Range("A5").Resize(numRows).Value = Application.Transpose(Application.Transpose(Application.Index(Rawdata, 0, i)))

Anyway, I think I will need to read up more about arrays on the internet as I am clearly lacking basic knowledge in this area!

Lucas

[Bob Phillips]

Bit confused about some elements/mechanics of how the code actually works as it seems a lot more complex than I thought it would be!

Without actually know much about it I thought naively that it would look something like:
[VBA]

Sub Array_Mockup()
Dim Rawdata
Rawdata = Worksheets("Sheet1").Range("a1:d200")
For i = 1 To 4
Worksheets("Sheet3").Range("a5:A205").Value = Rawdata.Range(Cells(1, i), Cells(200, i)).Value
Next i
End Sub
[/VBA]

So I presume you tried that, and did it work?

And in terms of the code I'm not sure that I understand how it working out the number of rows just via the follwoing line as there does not appear to be any count function etc?

numRows = UBound(Rawdata, 1) - LBound(Rawdata, 1) + 1

Maybe no count, but it is doing arithmetic. It takes the lower bound of the rows dimension from the upper bound, plus 1 to be inclusive, to get that count. It is just like counting the number of rows between 11 and 20 as Row(20) - Row(11) +1.

And not sure why any data needs to be transposed in the following line?

Worksheets("Sheet3").Range("A5").Resize(numRows).Value = Application.Transpose(Application.Transpose(Application.Index(Rawdata, 0, i)))

Anyway, I think I will need to read up more about arrays on the internet as I am clearly lacking basic knowledge in this area!

Maybe the code in this form can help explain it

[VBA]Sub Array_example()
Dim Rawdata As Variant
Dim ary As Variant
Dim numRows As Long
Dim i As Long

Rawdata = Worksheets("Sheet1").Range("a1:d200")
numRows = UBound(Rawdata, 1) - LBound(Rawdata, 1) + 1
For i = LBound(Rawdata, 2) To UBound(Rawdata, 2)

ary = Application.Index(Rawdata, 0, i)
ary = Application.Transpose(ary)
Worksheets("Sheet3").Range("A5").Resize(numRows).Value = Application.Transpose(ary)
'do your stuff
Next i
End Sub
[/VBA]

The first setting of ary is getting column i of the Rawdata array. I am using the Excel INDEX function to get an array of a single column. At this stage, the intermediate array is still a 2-dimensional array, so needs more work.

The second setting of ary uses the excel TRANSPOSE function to turn it into a 1-dimensional array. This is good, but it you want to load a column, and this is a row array.

So the third setting of ary again uses the excel TRANSPOSE function to turn a row array into a column array. This is a simple load into the worksheet range.

[LucasLondon]

Thanks XLD for explanantion of the code. This combined with some internet reading is helping to make more sense of the code.

Lucas

[LucasLondon]

Hi,

I've been trying to expand my code requirments and have hit a brick wall. Would I need to use the same process/code setup as suggested previously if want to do something like this?

Basically I'm reading in two arrays - rawdata sheet and finaldata. I am then taking the values (strings) in the first column of the finadata array, looking them up in the first row of the rawdata array and then copy over that entire column where the match is found.

This look up process seems to work fine using the code below but right now I can only get it to return a single value from the matched column in the rawdata array instead of all of the column.

I suspect I will need to utilise the same approach as before but would be great if someone can confirm and the best way of doing it.

Thanks

Lucas

[VBA]
Sub A000000A1A1A_Test()
Dim Rawdata, finaldata As Variant
Rawdata = Sheets("X Variables").Range("C1:AY146").Value
finaldata = Sheets("Build-Linear").Range("AA1:AG20")
For i = 2 To 20
potvar = finaldata(i, 1) 'loops through all elements in the array

For k = 1 To UBound(Rawdata)
If Rawdata(1, k) = potvar Then
Worksheets("Sheet1").Range("A1:a146").Value = Rawdata(1, k)

End If
Next k
Next i
End Sub
[/VBA]

[snb]

Basically you need:

[vba]Sub snb()
sp = Range("C1:G30")
sn = Range("AA1:AG20")

For j = 1 To UBound(sn)
Range("A1").Resize(UBound(sp)) = Application.Index(sp, 0, Application.Match(sn(j, 1), Application.Index(sp, 1), 0))

Stop

Next
End Sub[/vba]

i.e.

Lookup the respective values in AA1:AA30 in the row C1:G30 and save in column A the values of the column in range C1:C30 that has in it's first row the looked up value.

[LucasLondon]

Thanks SNB - that appears to work as I wanted.

Instead of pasting the data into a sheet is it possible to store it as a variable that I can directly use in a function?

For example: application.worksheetfunction.Average(Variable) instead of specifying range in the formula.

So where Variable would be something like:

[VBA] Variable = Resize(UBound(Rawdata)) = Application.Index(Rawdata, 0, Application.Match(Varlist(j, 1), Application.Index(Rawdata, 1), 0))
[/VBA]

Thanks,

Lucas

Latest code
[VBA]
Sub A00snb121()

Rawdata = Sheets("X Variables").Range("C1:AY146") 'Raw data Variables
Varlist = Sheets("Build-Linear").Range("AA1:AG20") 'List of Variables to consider for model
For j = 1 To UBound(Varlist)
Sheets("Sheet1").Range("a1").Resize(UBound(Rawdata)) = Application.Index(Rawdata, 0, Application.Match(Varlist(j, 1), Application.Index(Rawdata, 1), 0))
Stop

Next j
End Sub
[/VBA]

[snb]

[VBA]Sub snb()
sp = Range("C1:G30")
sn = Range("K1:K4")

For j = 1 To UBound(sn)
MsgBox Application.Average(Application.Index(sp, 0, Application.Match(sn(j, 1), Application.Index(sp, 1), 0)))
Next
End Sub[/VBA]

[LucasLondon]

Thanks SNB,

I was hoping to assign the output from the array to a variable so that I can use it in any functions/formulas throughout the rest of the code such as:

Application.Correl(YVar, #)

where # would be the array returned from:

(Application.Index(sp, 0, Application.Match(sn(j, 1), Application.Index(sp, 1), 0)))

Is that possible?

Thanks,

Lucas

[snb]

That's exactly what I showed/illustrated.

if you replace msgbox by 'x3=' the result wil be stored into the varaible 'x3'.

[LucasLondon]

Ok, this approach works with standard excel functions but not a custom function I am using as part of an add in.

The following returns "object variable or with block variable not set"

x = Application.Index(Rawdata, 0, Application.Match(Varlist(j, 1), Application.Index(Rawdata, 1), 0))

But If I code x as:

Set x = Sheets("sheet1").Range("g1:g146")

This works fine but when using the below it returns an "Object Required 424" error:

Set x = Application.Index(Rawdata, 0, Application.Match(Varlist(j, 1), Application.Index(Rawdata, 1), 0))


I think the function requires input varaibles to be defined as a ranges. Is there way to convert the output from x into a range?

Thanks,

Lucas

[Bob Phillips]

I am really struggling to see what your problem has too do with A1/R1C1 notation, but maybe this will work for you

Set x = Application.Index(Rawdata, 0, Application.Match(Varlist(j, 1), Application.Index(Rawdata, 0, 1), 0))

This is a bit of a punt though, I don't know your data, I can't see why the second has Application.Index and has no Sheets, so likely more detail required.