Ranges and Arrays - Passing values

[Don Lloyd]

Hi,

I can populate an array from a range quite easily, e.g.

Dim TestArray as Variant
TestArray = Range("C5").CurrentRegion.Value

Is there a way to pass values back to the range in a similar fashion ?
That is, without referring to the individual Array and Cell co-ordinates.

regards,
Don

 

[Don Lloyd]

Hi again,

Sorry to have bothered you but Problem solved. e.g.

Range("C5:F13").Value = TestArray

Thanks and regards,
Don

 

[Dana DeLouis]

Just to mention an idea. With the use of "CurrentRegion," your array size
is unknown. However, the output range (C5:F13) is "hard coded." See if
you could use this general idea.

Sub Demo()
Dim TestArray
TestArray = Range("C5").CurrentRegion.Value
'// Your code here...then

Range("C5").Resize _
(UBound(TestArray, 1), UBound(TestArray, 2)) = TestArray
End Sub

HTH

 

[Alan Beban]

Just to mention an idea. With the use of "CurrentRegion," your array size
is unknown. However, the output range (C5:F13) is "hard coded." See if
you could use this general idea.

Sub Demo()
Dim TestArray
TestArray = Range("C5").CurrentRegion.Value
'// Your code here...then

Range("C5").Resize _
(UBound(TestArray, 1), UBound(TestArray, 2)) = TestArray
End Sub

HTH

Or if TestArray might be (or have been changed by your code to) a
0-based array, substitute

Range("C5").Resize(UBound(TestArray,1)-LBound(TestArray,1)+1, _
UBound(TestArray,2)-LBound(TestArray,2)+1) = TestArray

Alan Beban

 

[Don Lloyd]

Thank you Dana and Alan for your useful and informative notes.

regards
Don