VBA .resize(ubound(arr)) returning only first value of array

[ouvay]

Hello, I have this code ...

Sub Pivot_Replace()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets("Batch Processing & Hold List")
Dim salesList As Worksheet: Set salesList = wb.Worksheets("Sales List Import")


Dim lr As Long, i As Long, j As Long, a As Variant, cancel() As Variant

lr = salesList .Cells(salesList .Rows.Count, 1).End(3).Row
a = salesList.Range("A2:R" & lr).Value
ReDim cancel(1 To lr) As Variant

For i = LBound(a) To UBound(a)
    If a(i, 9) <> Empty Then
        j = j + 1
        cancel(j) = a(i, 3)
    End If
Next i

ws.Range("T7").Resize(UBound(cancel)).Value = cancel

End Sub

but when the result I get printed back is only the first position of the array... for example..

if the first value in the array is AAA... the entire range will have just AAA printed back.. and not the actual values..

I can see the whole array successfully calculated in the watch window.. but the return to range bit is bugging out...
any tips?

 

[Alex Blakenburg]

You have cancel as a single dimension array.
Try putting
= Application.Transpose(cancel)

 

[ouvay]

You have cancel as a single dimension array.
Try putting
= Application.Transpose(cancel)

Thank you Alex! Once again you've come through with some grade A solutions! :D

 

[Alex Blakenburg]

In case you are interested, if you Redim as 2 dimension.
ReDim cancel(1 To lr, 1 to 1) As Variant
Load it as
cancel(j, 1) =
Then you won't need the transpose.
Note: transpose has a max and caps out at around 60k records (don't quote me on the number, I am texting from my mobile )

 

[ouvay]

In case you are interested, if you Redim as 2 dimension.
ReDim cancel(1 To lr, 1 to 1) As Variant
Load it as
cancel(j, 1) =
Then you won't need the transpose.
Note: transpose has a max and caps out at around 60k records (don't quote me on the number, I am texting from my mobile )

good to know! I'm currently capping out at 20k rows so I don't think it will come to that anytime soon! thanks for the heads-up though!