output arrays

[MetLife]

Hi,

I have some code to output arrays. But the problem is that when the column changes I need to update all the code.

My question - is there some way to re-write this so that I can use offset and only update a single line of code?

Range("BO39:BO116").Value = Application.Transpose(aa)
Range("BP39:BP116").Value = Application.Transpose(cc)
Range("BQ39:BQ116").Value = Application.Transpose(dd)
Range("BR39:BR116").Value = Application.Transpose(ee)
Range("BS39:BS116").Value = Application.Transpose(ff)
Range("BT39:BT116").Value = Application.Transpose(gg)
Range("BU39:BU116").Value = Application.Transpose(hh)
Range("BV39:BV116").Value = Application.Transpose(x)
Range("BW39:BW116").Value = Application.Transpose(ii)
Range("BX39:BX116").Value = Application.Transpose(tt)
Range("BY39:BY116").Value = Application.Transpose(jj)
Range("BZ39:BZ116").Value = Application.Transpose(ss)

 

[Fluff]

Untested, but maybe something like

Dim Rng As Range
Dim i As Long
Dim ary As Variant
ary = Array(aa, cc, dd)
Set Rng = Range("BO39:BO116").Value
For i = 0 To 11
   Rng.Offset(, 0).Value = Application.Transpose(ary(i))
Next i

 

[MetLife]

I'm getting the error - object required? run-time error 424?

when it tries to execute:
Set Rng = Range("BO39:BO116").Value

 

[Fluff]

Oops, remove the .Value

 

[MetLife]

OK I see, I just changed the line to: Set Rng = Range("BO39:BO116")

and it works now.

 

[Fluff]

Glad to help & thanks for the feedback

 

[MetLife]

Thanks for you help!