The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of a
larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

Aha! It works if I declare "Dim rng as Range". Klunk!


----- original message -----

"JoeU2004" wrote in message
...
The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of
a larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

different data types. try:
rng.Value = Array(21, 22, 23)

Or if you want rng to = array then:

Dim rng As Variant
rng = Array(21,22,23)
Range("b2:d2") = rng



"JoeU2004" wrote in message
...
The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of
a larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

Hi,

try it like this

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)
End Sub

Mike

"JoeU2004" wrote:

The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of a
larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

You're missing the Value property. Edit as follows:

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)
End Sub

"JoeU2004" wrote in message
...
The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of
a larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

Hi

The rng is implicit declared as a Variant, so the array is just
assigned to the rng variable.

To make the it work declare rng as Range, or use rng.Value=Array(...).
Using both will also work.

Sub testit()
Dim rng as Range
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)
End Sub

Hopes this helps.

---
Per

On 6 Maj, 22:14, "JoeU2004" wrote:
The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). *(Part of a
larger macro.)

Why doesn't the following work? *More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

"Mike H" wrote:
try it like this
[....]
Dim rng
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)

Please discuss the semantic differences between that solution and mine,
namely:

Dim rng as Range
set rng = Range("b2:d2")
rng = Array(21,22,23)

Are these simply two equivalent ways to do the same thing? Or is mine
fundamentally different (how)?

I'm not clear on when to use a variant v. Range, and when I need the Value
qualifier.


----- original message -----

"Mike H" wrote in message
...
Hi,

try it like this

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)
End Sub

Mike

"JoeU2004" wrote:

The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of
a
larger macro.)

Why doesn't the following work? More importantly, how can I make it
work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

One more...

Option Explicit
Sub testit1()
Dim DestCell As Range
Dim myArr As Variant

myArr = Array(21, 22, 23)
Set DestCell = ActiveSheet.Range("B2")
DestCell.Resize(1, UBound(myArr) - LBound(myArr) + 1).Value = myArr

End Sub

Or vertically:

Option Explicit
Sub testit3()
Dim DestCell As Range
Dim myArr As Variant

myArr = Array(21, 22, 23)

Set DestCell = ActiveSheet.Range("B3")

DestCell.Resize(UBound(myArr) - LBound(myArr) + 1, 1).Value _
= application.transpose(myArr)

End Sub




JoeU2004 wrote:

The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of a
larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

--

Dave Peterson

"Per Jessen" wrote:
The rng is implicit declared as a Variant, so the array
is just assigned to the rng variable.

To make the it work declare rng as Range, or use rng.Value

Oh, I think I understand now. Let me parrot it back to see if I got it now.

When I declared it as Variant (implicitly) and assigned a Range to it, I
made the object type Range, as intended.

But by assigning Array subsequently, VBA interpreted that as if I intended
to change the object type.

By using ".Value", I am clearly telling VBA that I want to treat the object
as Range. (Well, it is telling VBA that I want to use the current
variable's type, which I hope has a member Value.)

But it sounds like it is better practice for me to declare rng as Range,
since that it is my intent.

Thanks to all who responded.


----- original message -----

"Per Jessen" wrote in message
...
Hi

The rng is implicit declared as a Variant, so the array is just
assigned to the rng variable.

To make the it work declare rng as Range, or use rng.Value=Array(...).
Using both will also work.

Sub testit()
Dim rng as Range
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)
End Sub

Hopes this helps.

---
Per

On 6 Maj, 22:14, "JoeU2004" wrote:
The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of a
larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub

Hi

You got it right,

And yes you should always declare all your variables as the desired
type, see the site below

http://www.ozgrid.com/VBA/variables.htm

Regards,
Per


On 6 Maj, 22:41, "JoeU2004" wrote:
"Per Jessen" wrote:
The rng is implicit declared as a Variant, so the array
is just assigned to the rng variable.

To make the it work declare rng as Range, or use rng.Value

Oh, I think I understand now. *Let me parrot it back to see if I got it now.

When I declared it as Variant (implicitly) and assigned a Range to it, I
made the object type Range, as intended.

But by assigning Array subsequently, VBA interpreted that as if I intended
to change the object type.

By using ".Value", I am clearly telling VBA that I want to treat the object
as Range. *(Well, it is telling VBA that I want to use the current
variable's type, which I hope has a member Value.)

But it sounds like it is better practice for me to declare rng as Range,
since that it is my intent.

Thanks to all who responded.

----- original message -----

"Per Jessen" wrote in message

...
Hi

The rng is implicit declared as a Variant, so the array is just
assigned to the rng variable.

To make the it work declare rng as Range, or use rng.Value=Array(...).
Using both will also work.

Sub testit()
Dim rng as Range
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)
End Sub

Hopes this helps.

---
Per

On 6 Maj, 22:14, "JoeU2004" wrote:



The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of a
larger macro.)

Why doesn't the following work? More importantly, how can I make it work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

The failure to dimension rng defaults it to variant and simply makes rng=array

Mike

"JoeU2004" wrote:

"Mike H" wrote:
try it like this
[....]
Dim rng
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)

Please discuss the semantic differences between that solution and mine,
namely:

Dim rng as Range
set rng = Range("b2:d2")
rng = Array(21,22,23)

Are these simply two equivalent ways to do the same thing? Or is mine
fundamentally different (how)?

I'm not clear on when to use a variant v. Range, and when I need the Value
qualifier.


----- original message -----

"Mike H" wrote in message
...
Hi,

try it like this

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng.Value = Array(21, 22, 23)
End Sub

Mike

"JoeU2004" wrote:

The following does what I want:

Sub testit()
Range("b2:d2") = Array(21, 22, 23)
End Sub

But I would prefer to use a variable instead of Range("b2:d2"). (Part of
a
larger macro.)

Why doesn't the following work? More importantly, how can I make it
work?

Sub testit()
Dim rng
Set rng = Range("b2:d2")
rng = Array(21, 22, 23)
End Sub