Array to Range assignments behave different going horizontally and vertically

[gimme_this_gimme_that]

The following sub creates

1
1
1

Sub test()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
s.Activate
Dim v
v = Array(1, 4, 6)
Dim r As Range
Set r = s.Range(s.Cells(1, 1), s.Cells(3, 1))
r.Value = v
End Sub

When I change the r assignment to

Set r = s.Range(s.Cells1,1),s.Cells(1,3)

I get

1 4 6

What is the logic in Excel that makes it treat horizontal assignments
different from vertical ones?

Thanks.

 

[Barb Reinhardt]

What is it you want to do and we can help.

 

[Alan Beban]

In the first case you are trying to enter a horizontal array into a
vertical range.

Alan Beban

 

[gimme_this_gimme_that]

Thanks Alan,

Is it possible to create a vertical array?

 

[JE McGimpsey]

One way:

r.Value = Application.Transpose(v)

 

[Alan Beban]

For me this throws an "Object required" error.

r = Application.Transpose(v) works

Alan Beban

 

[JE McGimpsey]

Hmm... what version of XL?

Is r not defined as an object, or is it Application?

r.Value = Application.Transpose(v)

works fine for me in XL04 when plugged into the OP's code.

 

[Alan Beban]

xl2002. I ran

Sub abtest3()
v = Array(2, 4, 8)
s = Array(2, 8, 11)
r.Value = Application.Transpose(v)
'Range("a1:a3") = Application.Transpose(v)
'Range("b1:b3") = Application.Transpose(s)
End Sub

If I insert Dim r as Range or Dim r as Object at the begining I get the
"Object variable or With block variable not set" error

Alan Beban

 

[JE McGimpsey]

Not sure I understand what you're doing...

Your code doesn't Dim or Set r, so I would expect an "Object required"
error (assuming no Option Explicit, which I always use).

I'd expect the "Object variable...not set" error if you inserted either
Dim statement, since, again, the code doesn't actually Set r to a range.

The OP's code set the r range object variable to a range of cells before
trying to assign the variant containing the array to r.Value:

Sub test()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
s.Activate
Dim v
v = Array(1, 4, 6)
Dim r As Range
Set r = s.Range(s.Cells(1, 1), s.Cells(3, 1))
r.Value = v
End Sub

I was giving a suggestion only for the line after that...

 

[Alan Beban]

I was responding to the Op's question in his posting on 5/28/07 at 5:59:

"Is it possible to create a vertical array?" to which I thought you were
responding.

Wouldn't your code, then, load a vertical range rather than create a
vertical array?

Alan Beban