How to return Array() to range as variable?
 

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

How to return Array() to range as variable?
 

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

How to return Array() to range as variable?
 

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

How to return Array() to range as variable?
 

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

How to return Array() to range as variable?
 

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

How to return Array() to range as variable?
 

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

How to return Array() to range as variable?
 

"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

How to return Array() to range as variable?
 

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

How to return Array() to range as variable?
 

"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

How to return Array() to range as variable?
 

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 -

How to return Array() to range as variable?
 

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