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.

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

" wrote:

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.

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

Alan Beban
wrote:
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.

Thanks Alan,

Is it possible to create a vertical array?

On May 28, 5:47 pm, Alan Beban <unavailable wrote:
In the first case you are trying to enter a horizontal array into a
vertical range.

Alan Beban

One way:

r.Value = Application.Transpose(v)

In article .com,
"
wrote:

Is it possible to create a vertical array?

For me this throws an "Object required" error.

r = Application.Transpose(v) works

Alan Beban

JE McGimpsey wrote:
One way:

r.Value = Application.Transpose(v)

In article .com,
"
wrote:

Is it possible to create a vertical array?

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.

In article ,
Alan Beban <unavailable wrote:

For me this throws an "Object required" error.

r = Application.Transpose(v) works

Alan Beban

JE McGimpsey wrote:
One way:

r.Value = Application.Transpose(v)

In article .com,
"
wrote:

Is it possible to create a vertical array?