WRAPCOLS in VBA

[Adeel1]

Hi All

why below code isn't working showing error in last line.

Sub test()
Dim xx()

xx = Array("South", "North", "Center", "North", "Center", "South", "Center", "South", "North", "South", "North", "Center", "North", "Center", "South")

Z = Application.Transpose(xx)

x1 = Application.Evaluate("=WRAPCOLS(" & Z & ", 3)")

End Sub

 

[JEC]

You need a range instead of an array. Here your work around

Sub test()
 Dim xx, x1
 With Range("ZZ1")
    xx = Array("South", "North", "Center", "North", "Center", "South", "Center", "South", "North", "South", "North", "Center", "North", "Center", "South")
   .Resize(UBound(xx)) = Application.Transpose(xx)
    x1 = Application.Evaluate("WRAPCOLS(" & .CurrentRegion.Address & ", 3)")
   .CurrentRegion.ClearContents
 End With
End Sub

 

[Adeel1]

You need a range instead of an array. Here your work around

Sub test()
 Dim xx, x1
 With Range("ZZ1")
    xx = Array("South", "North", "Center", "North", "Center", "South", "Center", "South", "North", "South", "North", "Center", "North", "Center", "South")
   .Resize(UBound(xx)) = Application.Transpose(xx)
    x1 = Application.Evaluate("WRAPCOLS(" & .CurrentRegion.Address & ", 3)")
   .CurrentRegion.ClearContents
 End With
End Sub

thx JEC for your help and info.

 

[JEC]

You're welcome, here a pure VBA approach. Array 'ary' contains the output

Sub jec()
 Dim ar, j As Long
 ar = Array("South", "North", "Center", "North", "Center", "South", "Center", "South", "North", "South", "North", "Center", "North", "Center", "South")
 ReDim ary(UBound(ar), 4)
 For j = 0 To UBound(ar) - 1
   ary(j \ 5, j Mod 5) = ar(j)
 Next
End Sub

 

[Adeel1]

thx for your idea and support.