How to return multiple values in an Array - Excel VBA

Question

I am trying to return multiple values stored in an array. [Image of array] I'm not really sure how to describe it, but what i want to do is output the values like 0 in A1, 37.2 in B1, 5.51 in C1, 0.498 in D1, 4.17 in A2, 36.7 in B2, etc.

My current code is

For j = 1 to nw
    For k = 1 to 4
       tWB.Sheets("Data").Cells(j, k) = A(j)
    Next k
Next j

This is my first time posting so i am unsure of the posting etique, so if more information is required I can try and provide it.

Here is the entire code

    Option Explicit
    Option Base 1
    Sub DataMerge()
    Dim FileNames As Variant, A() As Variant
    Dim i As Integer, nw As Integer, j As Integer, k As Integer
    Dim UserRange As Range, ImportRange As String
    Dim tWB As Workbook, aWB As Workbook
    Set tWB = ThisWorkbook
    FileNames = Application.GetOpenFilename(FileFilter:="Excel Filter(*.csv),*.csv", Title:="Open File(s)", MultiSelect:=True)
    Workbooks.Open FileNames(1)
    Set UserRange = Application.InputBox(Prompt:="Select Range: ", Title:="Import Range", Type:=8)
    ImportRange = UserRange.Address
    nw = UBound(FileNames)
    ReDim A(nw) As Variant
    For i = 1 To nw
        Workbooks.Open FileNames(i)
        Set aWB = ActiveWorkbook
        A(i) = aWB.Sheets(1).Range(ImportRange)
        aWB.Close SaveChanges:=False
    Next i

    tWB.Activate
    For j = 1 to nw
        For k = 1 to 4
           tWB.Sheets("Data").Cells(j, k) = A(j)
        Next k
    Next j
   End Sub

you have a one dimension array of two dimension arrays. How are you loading your array? — Scott Craner
– Scott Craner, Commented Feb 27, 2020 at 21:19
Is the user always selecting a single contiguous row of cells? — Tim Williams
– Tim Williams, Commented Feb 27, 2020 at 21:44

JNevill · Accepted Answer · 2020-02-27 21:42:23Z

4

Your code is VERY close. Instead:

For j = 1 to nw
    For k = 1 to 4
       tWB.Sheets("Data").Cells(j, k) = A(j)(k,1)
    Next k
Next j

edited Feb 27, 2020 at 21:42

answered Feb 27, 2020 at 21:35

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Craner Over a year ago

If you look at the picture, A is an array of arrays. So the A(j,k) will give an error as there is no second dimension in A.

Scott Craner Over a year ago

you want = A(j)(k,1)

JNevill Over a year ago

Oh Naughty! @Scott I will remove the second option and update the first.

Scott Craner Over a year ago

And for the second, keep in outer loop: tWB.Sheets("Data").Range("A" & j).Resize(1, 4).Value = Application.Transpose(A(j))

Collectives™ on Stack Overflow

How to return multiple values in an Array - Excel VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related