How to output an array variable into range starting say from sheets("test").range("a1")? Would there be any way to output array into NamedRange without previous dump into cells?
Is there a way without looping?
Update. The number of elements in array is not constant and changes upon refresh or user input. The focus of my question is how to resize the range so it fits the array.
Simple examples for a 1-D array
array to row:
Sub arrayTest()
Dim arr(1 To 3) As String
Dim r1 As Range
Set r1 = Range("A1:C1")
arr(1) = "larry"
arr(2) = "moe"
arr(3) = "curly"
r1 = arr
End Sub
or array to column:
Sub arrayTest2()
Dim arr(1 To 3) As String
Dim r1 As Range
Set r1 = Range("A1:A3")
arr(1) = "larry"
arr(2) = "moe"
arr(3) = "curly"
r1 = Application.WorksheetFunction.Transpose(arr)
End Sub
A 2-D array is equally easy:
Sub arrayTest3()
Dim arr(1 To 3, 1 To 2) As String
Dim r1 As Range
Set r1 = Range("A1:B3")
arr(1, 1) = "larry"
arr(2, 1) = "moe"
arr(3, 1) = "curly"
arr(1, 2) = "gary"
arr(2, 2) = "student"
arr(3, 2) = "whatever"
r1 = arr
End Sub
EDIT#1:
Say we have an arbitrary 1-D array, either zero-based or one-based, and we want to push it into a row starting with cell B9:
Sub arrayTest4()
Dim r1 As Range, U As Long, L As Long, rBase As Range
Set rBase = Range("B9")
arr = Array("qwert", 1, 2, 3, 4, "ytrew", "mjiop", "nhy789")
L = LBound(arr)
U = UBound(arr)
Set r1 = rBase.Resize(1, U - L + 1)
r1 = arr
End Sub
Range("C3").Resize(rowCount,colCount) - where "C3" is the top left cell - and set rowCount/colCount using Ubound on your array.
Here go two useful snippets I have build based on Gary's Student answer. Thank you Gary's Student!
Sub Array2DToRange()
Dim Arr()
ReDim Arr(0 To 2, 0 To 0) 'handles also Arr(1 to 3, 1 to 1)
Arr(0, 0) = "Spinosaur"
Arr(1, 0) = "T-Rex"
Arr(2, 0) = "Triceratops"
Dim R As Long, C As Long
R = UBound(Arr, 1) - LBound(Arr, 1) + 1
C = UBound(Arr, 2) - LBound(Arr, 2) + 1
Range("k1").Resize(R, C).Value = Arr
End Sub
Sub Array1DToRange()
Dim Arr()
ReDim Arr(0 To 2) 'handles also Arr(1 to 3)
Arr(0) = "Spinosaur"
Arr(1) = "T-Rex"
Arr(2) = "Triceratops"
[a1].Resize(UBound(Arr) - LBound(Arr) + 1, 1) = Application.Transpose(Arr)
End Sub
