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.

"(E-Mail Removed)" 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
(E-Mail Removed) 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 <(E-Mail Removed)>,
"(E-Mail Removed)" <(E-Mail Removed)>
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 <(E-Mail Removed)>,
> "(E-Mail Removed)" <(E-Mail Removed)>
> 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 <(E-Mail Removed)>,
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 <(E-Mail Removed)>,
> > "(E-Mail Removed)" <(E-Mail Removed)>
> > wrote:
> >
> >> Is it possible to create a vertical array?

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 wrote:
> 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 <(E-Mail Removed)>,
> 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 <(E-Mail Removed)>,
>>> "(E-Mail Removed)" <(E-Mail Removed)>
>>> wrote:
>>>
>>>> Is it possible to create a vertical array?

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...



In article <evN#(E-Mail Removed)>,
Alan Beban <unavailable> wrote:

> 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

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

JE McGimpsey wrote:
> 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...
>
>
>
> In article <evN#(E-Mail Removed)>,
> Alan Beban <unavailable> wrote:
>
>> 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