0

I'm working on a subroutine to perform some calculations and collect the results from several disparate tables (Of equal size and similar content) and then dump them into a worksheet. At the moment my driver looks like this:

Sub Driver()
    Dim works() As Variant
    Dim rng As Range
    Dim ws as worksheet

    'This reliably works and outputs an
    works = GenerateWorksArray()
    Set ws = Worksheets.Add
    Set rng = ws.Range("A1:P1").Resize(UBound(works, 1))

    'This is where it all breaks down
    rng = works

end sub

The array passed back from GenerateWorksArray is a 1,500 x 16 variant array that contains strings, currencies and doubles. For some reason, when i'm outputting the array with rng = works, Only the first 156 rows are correctly outputted and from then on in I get nothing.

The code appears to be still running and if I click on a cell in that range I can see that the formula bar is wildly flickering. When I try and stop or break the code from executing excel just crashes. Has anyone experienced something similar and got a working solution together because I'm stumped?

MMerry
  • 334
  • 2
  • 11
  • 2
    Is there any event code in the worksheet? – Rory Jun 16 '14 at 06:51
  • see [2d array from range](http://stackoverflow.com/questions/18481330/2-dimensional-array-from-range/18481730#18481730) –  Jun 16 '14 at 07:01
  • What excel version are you using? – L42 Jun 16 '14 at 09:00
  • 2
    isn't `ws.Range("A1:P1").Resize(UBound(works, 1))` large of 17 columns ? change `,1` to `,0`. Also verify that works really has the 1500 rows data. i would add `application.screnudpating=false` and `.enableevents=false` for speed. it might not change anything but i usually write `rng.value = works`. – Patrick Lepelletier Jun 16 '14 at 09:53
  • I found that there was an error deep in the data, namely a formula that wasn't working and was being stored as text with an = at the front of it. This caused the array to freak out when I assigned it to the range, so moral of the story is check your data! @PatrickLepelletier I confirmed that the syntax for re-sizing the array worked as expected by printing the `.address` property. `.Resize(UBound(works, 1))` will re-size the first dimension to the number of rows in works. Thanks for the help! – MMerry Jun 16 '14 at 22:25

1 Answers1

1

Try ws.Range("A1").Resize(UBound(works, 1), UBound(works, 2))

Blackhawk
  • 5,984
  • 4
  • 27
  • 56
  • 1
    Can you provide a little more information, *why* the OP should try this? – Schorsch Jun 16 '14 at 17:26
  • 1
    @Schorsch Per Patrick's comment, the "A1:P1" clouds the issue somewhat. If the array is of very different dimensions than the OP is expecting, the code I propose would reveal that. If I post it as a comment and it works for the OP, he will leave a comment saying "thanks!" and the question would join the ranks of orphaned, unanswered questions. My hope is that either the question will be answered by this code OR that this code will reveal the actual cause of the OP's problem. – Blackhawk Jun 16 '14 at 17:51
  • @Blackhawk, It turns out that the method was sound but the underlying array had errors in it that manifested when assigned to a range. As a seasoned StackOverflow user what do you recommend I do with this question next? – MMerry Jun 16 '14 at 22:30