Writing Array to Range

[billandrew]

Afternoon

I've been attempting to understand arrays and in particular how to write an array (see below) to a range.

This code does not include the last element "July" - not sure why?

Thanks for any help/explanation

Dim arr As Variantarr = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "July")


[D2].Resize(UBound(arr)) = Application.Transpose(arr)

If I utilize this code, which places all values in the array then copies to range F1. I have no problem.

Dim myarray As Variant



myarray = Range("C1", Range("C" & Rows.Count).End(xlUp)).Value


[F1].Resize(UBound(myarray)) = myarray

 

[offthelip]

I suspect yor problem is because by default excel will define an array starting with index zero, so your array has elements 0 to 6
while if you load an array from a worksheet is will by default be starting at 1 , this is because there isn 't a row zero on trhe worksheet

 

[billandrew]

Even when I specified specific criteria?

 

[Rick Rothstein]

offthelip is correct about the reason why you are "missing" the last element of your array. However, since your array elements are fixed text constants, there is another way you can populate the arr variable so you don't need the Transpose function call. Note that there is a slight variation for an array that will be assigned down a column of cells (semi-colon delimiter) as opposed to across a row of cells (comma delimiter).

' Semi-colon delimiter
Sub PutArrayElementsDownTheColumn()
  Dim arr As Variant
  arr = [{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"July"}]
  [D2].Resize(UBound(arr)) = arr
End Sub

' Comma delimiter
Sub PutArrayElementsAcrossTheRow()
  Dim arr As Variant
  arr = [{"Jan","Feb","Mar","Apr","May","Jun","July"}]
  [F1].Resize(, UBound(arr)) = arr
End Sub

 

[billandrew]

Thank you Mr. Rothstein
question if I added a Blank " " as a work around. Then the blank would be the last element

 

[Rick Rothstein]

Thank you Mr. Rothstein
question if I added a Blank " " as a work around. Then the blank would be the last element

If you are talking about using the Array function (as you originally posted), wouldn't it be easier (or at least as easy) to simply add one to the UBound function's return value as offthelip posted? If, on the other hand, you are talking about the alternatives I posted to using the Array function, there is no need to adjust anything as the arrays are two dimensional arrays starting with index number 1 for both dimensions.

 

[billandrew]

Great I'll figure out how to add 1 to ubound. Now I'll have 3 methods.