Filling a Range of Cells with Values

@J.Woolley

Ahhh, a little more has crept into my gray matter.

Allen's code had the issue, not me. I changed the DIM to Variant and deleted the (2) from the length and it worked perfectly.

Thank you
--------------------------------------------------------------------------------------------
Sub mike_2()

Dim sMyStrings() As Variant
sMyStrings = Array("Apples", "Oranges", "Artichokes")
Range("A1:C1") = sMyStrings

End Sub
--------------------------------------------------------------------------------------------

@Mike D.
The VBA Array(...) function must be assigned to a Variant variable or Variant array of undeclared size, so this is not allowed for two reasons:
    Dim A(2) as String
    A = Array("Apples", "Oranges", "Artichokes")
Note that Variant is the default type, so these are OK:
    Dim A() As Variant, B As Variant, C, D()
    A = Array("Apples", "Oranges", "Artichokes")
    B = Array(1, 2, 3)
    C = Array("Apples", "Oranges", "Artichokes")
    D = Array(1, 2, 3)
Also, be careful with Option Base; the default is 0, but it might have been declared as 1 in your module. The qualified function VBA.Array(...) is not affected by Option Base, so this always creates an array like A(0 to 2):
    A = VBA.Array("Apples", "Oranges", "Artichokes")
But this might create A(0 to 2) or A(1 to 3) depending upon Option Base:
    A = Array("Apples", "Oranges", "Artichokes")
See https://sites.google.com/view/MyExcelToolbox/

Thank you Peter and Willy.
I have been able to get it to work with the other code Allen posted, I was trying to figure out what I was doing wrong.
I experimented with the code and figured out a few things. I will try your suggestions for fun. Learn, learn learn.

What I am hoping is to figure out the syntax for the variable = Array(data, data, data).
I feel it should work but there is something simple I am missing.

Thanks again

@Peter Atherton

You can even write it as a one liner:

Sub t()
Range("A1:C1") = Split("Apples Oranges Artchokes")
End Sub

Mike D

Try this

Sub t()
Dim s
Dim sMyStrings As Variant

s = "Apples, Oranges,Artichokes"
sMyStrings = Split(s, ",")
Range("A1:C1") = sMyStrings
End Sub

I copied your code from above and ran into an error I cannot resolve.

Dim sMyStrings(2) As String
sMyStrings = Array("Apples", "Oranges", "Artichokes")
Range("A1:C1") = sMyStrings

Compile Error:
Can't assign to array

The editor highlights the sMyStrings variable as the culprit.
What am I missing? (Running 365)

I copied your code from above and ran into an error I cannot resolve.

Dim sMyStrings(2) As String
sMyStrings = Array("Apples", "Oranges", "Artichokes")
Range("A1:C1") = sMyStrings

Compile Error:
Can't assign to array

The editor highlights the sMyStrings variable as the culprit.
What am I missing? (Running 365)

In newer versions of Excel with dynamic arrays, here is yet another way using curly brackets like {...}.
For 3 columns in one row:
Range("A1").Formula2 = "={""Test1"",""Test2"",""Test3""}"
For 3 rows in one column:
Range("A1").Formula2 = "={""Test1"";""Test2"";""Test3""}"
Each results in an array formula of text constants instead of the Tip's array of constant text values.