Not getting desired results when moving a VBA array to a spread sheet

[JLoewen]

I have a VBA program for importing all the file names in a target folder into a spreadsheet for manipulation. I am using this for managing architectural specifications. The program appears to work (monitoring variables) until the last step where it is to pass the data to the spreadsheet. At that point, it places the 1st cell of the array into the spreadsheet the once for each cell. Results shown below along with code.

Public Sub GetFileNames()

Dim Result As Variant
Dim i As Integer
Dim MyFile, MyFso, MyFolder, MyFiles As Object
Dim FolderPath, FileExt As String
Dim outputarray As Variant

FolderPath = Worksheets("Sheet1").Range("A1").Value

Set MyFso = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFso.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files



ReDim Result(1 To MyFiles.Count)

i = 1
For Each MyFile In MyFiles
Result(i) = MyFile.Name

i = i + 1

Next MyFile

i = i - 1 

Worksheets("Sheet1").Range(Cells(3, 1), Cells(3 + i, 1)).Value = Result

End Sub

 

[Fluff]

try

With Worksheets("Sheet1")
   .Range(.Cells(3, 1), .Cells(3 + i, 1)).Value = Application.Transpose(Result)
End With

 

[Fluff]

A safer option would be

ReDim Result(1 To MyFiles.Count, 1 To 1)

i = 1
For Each MyFile In MyFiles
Result(i, 1) = MyFile.Name

i = i + 1

Next MyFile

i = i - 1

With Worksheets("Sheet1")
   .Range(.Cells(3, 1), .Cells(3 + i, 1)).Value = Result
End With
End Sub

 

[JLoewen]

No luck with either of those. For a 1-d array does VBA do those strictly horizontally instead of vertically? Would it work to effectively force a 2-d array? Something that is (n x 1) in size instead of just in size?

 

[Fluff]

Would it work to effectively force a 2-d array?

That's what the 2nd code is doing. In what way doesn't it work?

 

[JLoewen]

That's what the 2nd code is doing. In what way doesn't it work?

It worked perfectly once I corrected a syntax error of my own in the statement to redim the array. Thanks for the help!

 

[Fluff]

You're welcome & thanks for the feedback.