Writing Multi-Dimensional Array To Range

Hi!

I have a bit of a bizarre problem, and before I upload the workbook for people to look at, I'd like to see if this is problem that can be easily fixed just by someone hearing the issue:

I have a multi-dimensional array full of strings that I am writing to a range of several consecutive entire rows. When I run the macro, I see the values paste over the range momentarily the way I would like, but only for an instant because a split-second later the entire range is BLANK. All the cells in my range are immediately erased...and there is no more code in my macro after this part that could be doing it.

I've tried making the array of variant type and string type, and when I store strings into the array (that I get from other cells in the workbook) before I write it to the range, I've tried storing cell formulas and cell values.

Any suggestions? If this doesn't help, I can upload the workbook.

Thanks!

Dan

Hi Dan,

This sounds as if your code is a bit "enthusiastic" & is overwriting the data you want with blank strings. As you're using a multi-dimensional array, the first thing I'd look at is any FOR-NEXT loops that write data to your worksheet. I'd be surprised if anything associated with the variable type would produce the result you describe.

I'm happy to have a look at your workbook if you'd care to post it.

Regards,

Greg M

Hi Greg -

Thanks for the response - I'd love to have you take a look at it, but I think it's too big for this site...when I put it in a zip it's about 160 kb, and the max is 100 kb it said. Can I send it to you over e-mail?

Thanks so much!

Dan

Hi Dan,

Sure - send it to me at gmm131313@yahoo.com & we'll see what happens.

All the best,

Greg

Hi Dan,

Wow - impressive workbook!

Well, some bad news and some good news - the bad news is that I don't know enough about the application to be able to make things 100% RIGHT at this stage, but the good news (I hope) is that I can at least tell you exactly why and where things are starting to go WRONG.

First the "why": the satisfying thing for me (said he, smugly) is that my initial suspicions regarding the source of the problem were well-founded. When you mentioned that you were populating a range using the contents of a multi-dimensional array, I thought that the likely problem would be an inconsistency between the dimensions of the array and the dimensions of the range - this seems to be the case.

Now the "where": the second-last command of the "MoveMetric" routine is "rangeAll.Formula = metArray()" - i.e. where you populate the range using the array. The problem occurs here. At this point the range "rangeAll" is ALL OF rows 8:11 - i.e. a total of 1024 cells. However, at this stage the upper bound of "metArray's" first dimension is 4 (corresponding to the four rows in question), BUT the upper bound of "metArray's" second dimension is 143 which gives a total of only 572 values.

I've changed the "rangeAll.Formula = metArray()" command to read:

Please Login or Register  to view this content.

This has the effect of achieving the correct match between the dimensions of the range and those of the array, and it appears (to me) that the correct formulas are entered in the range as a result. I think that some additional work may be needed in relation to formatting, because the cells which contain the "sub-headings" (Availability, Sim Server ...) have bold text and are not indented - with luck this won't be a major task!

There appears however to be another error somewhere in your code. It's potentially quite serious, probably easy to fix, but maybe tedious to find, and I'm afraid I haven't tried to locate it yet. The problem is this - somewhere between the start of the "MoveMetric" routine and the second-last command in that routine, error handling has been disabled (On Error Resume Next) and has NOT been re-enabled. I discovered this by accident because I inserted some debugging code immediately before the second-last command but found that it produced no output at all - not even an error message. I suspected that the error handling had been disabled, inserted an "On Error GoTo 0" command immediately before my debugging code, ran the routine and it crashed when an error was encountered in MY debugging code - talk about serendipity!

So, we know that the error handling has been disabled, and has not been re-enabled where it should have been - obviously it should be re-enabled somewhere other than at the start of my debugging code! It's even possible that the missing formatting mentioned above is due to code errors being ignored as a result of error handling being disabled.

Anyway, I think that's enough to be going on with for the moment. It's a very interesting project and I'd be very interested to hear how you get on with it. I'm also happy to work along with you on it if you think I can be of any help.

Best regards for the time being,

Greg M

Greg -

Thanks a BUNCH. I had assumed that the smaller array would just write onto the range until it ran out of items, and that the leftovers would be untouched. I'll try that new line of code tonight and I'll check on that error handling issue, hopefully I can find out where the problem is.

From now on I'll just forward my boss' requests for changes to the workbook to you.

Dan

Hi Dan,

Thanks for the feedback - glad that things are starting to move in the right direction.

Please keep me posted as to your progress. No problem regarding other changes - if there's anything else you think I can help with, just shout.

Best regards,

Greg M