Having trouble transferring 2D array to Table

[RawlinsCross]

I have a table of 20 columns to which I want to drop an array of dimensions vArray(1 to 708, 1 to 20). The code below was working but just 'decided' to stop working. When I put a breakpoint before the transfer code I can see the entire array and ALL the data. But when I apply the transfer code only 2 columns are transferred. Has anyone come across this?

Here's the code:

Public Sub TransferArray(vData as Variant)  'Add watch for vData shows all the array columns are filled.

Set wSht as Worksheet
Set rRange as Range

Set wSht = ThisWorkbook.Worksheets("Data")
Set rRange = mwSht.Range("A8").Resize(UBound(vEmployees_, 1), UBound(vEmployees_, 2))   'A8 is the beginning for the databodyrange of the table

rRange.Value = vData

End Sub

When I check the table only two of the columns (out of 20) has been successfully transferred. I can see that the entire range (1 to 708, 1 to 20) has been selected.

So I'm forced to replace the rRange.Value = vData with the code below that does transfer the array (albeit much slower)

For i = LBound(vData, 1) To UBound(vData, 1)
    For j = LBound(vData, 2) To UBound(vData, 2)
        wSht.Cells(i + 7, j) = vData(i, j)
    Next j
Next i

Has anyone come across this?

 

[StephenCrump]

Set rRange = mwSht.Range("A8").Resize(UBound(vEmployees_, 1), UBound(vEmployees_, 2)) 'A8 is the beginning for the databodyrange of the table

What's vEmployees_?

If you want the range to fit vData, surely you should resize to the dimensions of vData?

(I also notice that you define wSht but then specify mwSht. Presumably this isn't causing a problem, but it has the potential to do so).

 

[RawlinsCross]

Terribly sorry - that was sloppy, that code should have read:

Public Sub TransferArray(vData as Variant)  'Add watch for vData shows all the array columns are filled.

Set wSht as Worksheet
Set rRange as Range

Set wSht = ThisWorkbook.Worksheets("Data")
Set rRange = wSht.Range("A8").Resize(UBound(vData, 1), UBound(vData, 2))   'A8 is the beginning for the databodyrange of the table

rRange.Value = vData

End Sub

 

[RawlinsCross]

Just an update on this issue. When I paused my One-Drive sync, the code worked as above. Does One-Drive mess things up?

 

[StephenCrump]

Does One-Drive mess things up?

In a word, no. If vData has dimensions (1 to 708, 1 to 20) there is no reason that syncing OneDrive should impact the dumping of vData to a similar-sized range.

It is quite possible though that vData is not populating the way you think it is, based on whatever other code you have running.

In your first post, you said:

When I put a breakpoint before the transfer code I can see the entire array and ALL the data. But when I apply the transfer code only 2 columns are transferred. Has anyone come across this?

What do you mean by "see the entire array and ALL the data"? What are you looking at? What matters here is what is in vData. Are you testing this?

And finally, when you post code, can we please see the code you're actually using so that we can replicate what you're doing. Clearly version 1 wasn't correct. And version 2 contains a couple of basic syntax errors, suggesting that the code you're running might be different to what's posted?

 

[RawlinsCross]

Hey Stephen... was away with no internet but this problem seems to pop up and as easily 'pop down'. Not sure what triggers the phenomenon. What I mean by 'seeing the entire array" is that when I put a breakpoint at the location

rRange.Value = vData

and I look through the 708 rows and 20 columns of the array, all the data is there. Why it transfers to rRange which is appropriately sized is beyond me.