Pasting VBA Array into Excel Range

Question

I know this question has been asked multiple times, but I am kinda stuck adopting the suggested solutions to my problem.

I have an array(0 to 4), that gets filled multiple times in a loop and should be pasted each time into a new line in excel.

Expected Output:

      A      |      B      |      C     |      D     |      E
1     X1     |      X2     |      X3    |      X4    |      X5

My code:

r i = 0 To iVal
Dim infoarr(0 To 4) As Variant
infoarr(0) = ws_src_agv.Cells(ref + i + 3, 2).Value 
infoarr(1) = ws_src_agv.Cells(ref + i + 4, 2).Value 
infoarr(2) = ws_src_agv.Cells(ref + i + 3, 1).Value 
infoarr(3) = ws_src_agv.Cells(ref + i + 3, 3).Value 
infoarr(4) = ws_src_agv.Cells(ref + i + 3, 7).Value 

lastR = ws_tgt_agv.Rows(Rows.Count).End(xlUp).Row

'First attempt:
ws_tgt_agv.Range(ws_tgt_agv.Cells(lastR + 1, 1), ws_tgt_agv.Cells(lastR + 1, 5)).Value = WorksheetFunction.Transpose(infoarr)

Output:

      A      |      B      |      C     |      D     |      E
1     X1     |      X1     |      X1    |      X1    |      X1

2nd attempt:

 ws_tgt_agv.Cells(lastR + 1, 1).Resize(UBound(infoarr, 1) + 1).Value = WorksheetFunction.Transpose(infoarr)

Ouput:

      A      |      B      |      C     |      D     |      E
1     X1     |             |            |            |      
2     X2
3     X3
4     X4
5     X5

if leaving the transpose argument at the end the same range gets filled with sloley X1.

Thanks for your help!

skkakkar · Accepted Answer · 2019-03-22 09:47:07Z

3

A 1-D array (both zero based and one based) is aligned like a single row with multiple columns. You don't need to transpose in order to put te array's values into the worksheet; you only need the correct size of target.

with ws_tgt_agv
  .Range(.Cells(lastR + 1, 1), .Cells(lastR + 1, 5)).Value = infoarr
end with

If you want to put the array's values into a single column of multiple rows then you need to transpose.

with ws_tgt_agv
  .Range(.Cells(lastR + 1, 1), .Cells(lastR + 6, 1)).Value = Application.Transpose(infoarr)
end with

edited Mar 22, 2019 at 9:47

skkakkar

2,8482 gold badges20 silver badges32 bronze badges

answered Mar 22, 2019 at 8:44

user11217663

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

2 Comments

Maeaex1 Over a year ago

Thanks! Worked perfectly.

skkakkar Over a year ago

@user11217663 edited typo on Application.Transpose line

Collectives™ on Stack Overflow

Pasting VBA Array into Excel Range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related