Populate an Excel Table with a One Dimensional array using VBA

Question

I am trying to populate a one column table in excel which I can connect to power query, using vba and a one dimensional array.

So the user puts a list into a textbox where each item is seperated by a semicolon, then it brings that into the array. So far I have :

Dim arrSIOCodes As Variant

arrSIOCodes = Split(tbSIOCodes, ";")

ThisWorkbook.Sheets("CAEATFA_SIO").Activate

Call ChangeTableToArray(tbl:=ThisWorkbook.Sheets("CAEATFA_SIO").ListObjects("tblSIOCodes"), ar:=arrSIOCodes)

and I got this sub from another stack overflow post but I need to customize it to my issue and I am having trouble

Sub ChangeTableToArray(tbl As ListObject, ar)
  Dim newRows As Long: newRows = 1 + UBound(ar, 1) - LBound(ar, 1)
  If Not tbl.DataBodyRange Is Nothing Then tbl.DataBodyRange.EntireRow.Delete
  If newRows > 1 Then tbl.HeaderRowRange.Resize(newRows - 1).Offset(2).EntireRow.Insert
   tbl.HeaderRowRange.Resize(newRows, 1 + UBound(ar, 1) - LBound(ar, 1)).Offset (1).value = ar
End Sub

This is what the current code is doing:

however this is what I need it to do:

I THINK you can do it with ... .value = Application.Transpose(ar). Oops - no, you can't, you are deliberately resizing the columns, not the rows. — YowE3K
– YowE3K, Commented Dec 19, 2017 at 23:51
@YowE3K that gets one step closer, now each column lists value1,value2,value3 but it still iterates across the same number of columns. I can't figure out how to reduce columns without breaking it. — SanomaJean
– SanomaJean, Commented Dec 19, 2017 at 23:55
@ja72 It's usually better to use Application.Transpose than WorksheetFunction.Transpose — YowE3K
– YowE3K, Commented Dec 19, 2017 at 23:57
@ja72 I guess the most notable thing would be that it is directly using VBA's Excel Application object, rather than having to pass the request to Excel itself to process and return. Mat's Mug would be better able to explain the differences between the two methods - he (I assume the mug is a "he"!) gets down into the guts of the way processing is done with some of the stuff he does for RubberDuck. — YowE3K
– YowE3K, Commented Dec 20, 2017 at 0:06

YowE3K · Accepted Answer · 2017-12-19 23:56:24Z

2

You are resizing the number of columns in the destination, but you only have one column (if you Transpose the array) to store. So do something like:

tbl.HeaderRowRange.Resize(newRows, 1).Offset (1).value = Application.Transpose(ar)

answered Dec 19, 2017 at 23:56

YowE3K

24k7 gold badges28 silver badges45 bronze badges

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

3 Comments

SanomaJean Over a year ago

Brilliant! I tried this, but since I didn't combine it with the addition of Application.Transpose, it was erroring out. But this works, thank you!!

YowE3K Over a year ago

@SanomaJean Without the Transpose, it shouldn't have errored out, but it would have simply filled up all the rows with the first value (which isn't very useful)

SanomaJean Over a year ago

Ah you're right, it's another thing I tried that was erroring- what you said was the result of this. I tried so many things I got all the wrong outputs mixed up haha. Thanks for the help!

K.Dᴀᴠɪs · Accepted Answer · 2017-12-20 00:03:21Z

0

About to go into a late meeting, so I threw this together quickly and it's untested. But this is just another way to look at it:

Sub ArrayToTable(ByVal ws As Worksheet, ParamArray YourArr() As Variant)

    Dim TmpArr As Variant, i As Long, LineNum As Long

    TmpArr = YourArr(0)

    LineNum = 1 'whatever method you use to find linenumbers

    For i = LBound(TmpArr) To UBound(TmpArr)
        ws.Cells(LineNum, 1).Value = TmpArr(i)
        LineNum = LineNum + 1
    Next

End Sub

answered Dec 20, 2017 at 0:03

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Collectives™ on Stack Overflow

Populate an Excel Table with a One Dimensional array using VBA

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related