The following code is within a module
Option Explicit
Private sub paste_arr()
Dim arr(2)
arr(0) = 2
arr(1) = 3
arr(2) = 5
with sh_array
.Range(.Cells(2,5), .Cells(4,5)) = arr
end with
Results in the following array
2
2
2
It seems that only the first item in the list is pasted but I want to paste the entire array.
Other solutions don't seem to use the index notation. For example the following
Option Explicit
Private Sub ZeroBased()
' ' 1. Either
' 'Dim arr(2) As Variant
' ' More correct is (Option Base related):
' Dim arr(0 To 2) As Variant ' ensure zero-based
' arr(0) = 2
' arr(1) = 3
' arr(2) = 5
' 2. Or
'Dim arr As Variant: arr = Array(2, 3, 5)
' More correct is (Option Base related):
Dim arr As Variant: arr = VBA.Array(2, 3, 5) ' ensure zero-based
Dim cCount As Long
'cCount = UBound(arr) - LBound(arr) 1 ' 2 - 0 1 = 3
' Since in this case LBound(arr) is 0, you can simplify:
cCount = UBound(arr) 1 ' 2 1 = 3
' A simple way to write the values to the Immediate window (Ctrl G).
Debug.Print Join(arr, ",") ' delimited
Debug.Print Join(arr, vbLf) ' one below the other
' Write to a range
With Sheet1
' Write to a one-row range:
.Range("A1").Resize(, cCount).Value = arr ' A1:C1 ' note the comma
' Write to a one-column range:
.Range("A3").Resize(cCount).Value = Application.Transpose(arr) ' A3:A5
End With
' .Resize(, cCount) is short for .Resize(1, cCount)
' .Resize(cCount) is short for .Resize(cCount, 1)
' Interestingly, you could easily write only the first 2 items
' (think first 100 out of 1000 items):
With Sheet1
' Write to a one-row range:
.Range("E1").Resize(, 2).Value = arr ' E1:F1 ' note the comma
' Write to a one-column range:
.Range("A7").Resize(2).Value = Application.Transpose(arr) ' A7:A8
End With
End Sub