How to return Array() to range as variable?

[JoeU2004]

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

 

[JoeU2004]

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


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

 

[JLGWhiz]

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

 

[Mike H]

Hi,

try it like this

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

Mike

 

[Howard31]

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

 

[Per Jessen]

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.

 

[JoeU2004]

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

 

[Dave Peterson]

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]

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

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 Jessen]

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

 

[Mike H]

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

Mike

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

Hi,

try it like this

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

Mike