Forum Home
Press F1
Thread ID: 27387	2002-11-21 07:38:00	Excel and filenames again!	Mike (15)	Press F1

Post ID	Timestamp	Content	User
100263	2003-12-17 21:19:00	Yes! "Ok, Ive found the problem. "X" should be a String not Long. I got a not responding crash now. " Sorry about that, I forgot that the list was filenames and was instead using a list of numbers to check that it worked (they are much easier to generate for testing purposes). x(n,1) is an array, but in this particular case is actually one-dimensional and not 2D. The code would work just as well if it were x(n). But I like the possibility of being able to easily generalise ... x(n,1) here is just a reading of the original filename list into memory, its much faster operating on it out of memory than it is taking it from the Excel cells for each operation.	rugila (214)
100264	2003-12-17 21:53:00	Hi Rugila, I found another couple of problems but Ive found a cure. In the Do While there needed to be a n = n+1 and if there were cells up to 65536 then it would bomb because it looks for cells(65537,1) so I used an exit do to kill it. I would say that " X " is a two dimensional array because your assigning two array dimensions ... x(65537, 1) not x(65537) :-) The amended code below. Well done Rugila, very slick - it only took 12 secs on my PC! :-) Im not sure I understand some parts though like... Cells(a + (c - 1) * (nr + 1) + 3, b + 2) = x(u, 1) ----NEW CODE---- Sub Ruglia() 'Original list entered down Column A, starting in Row 2 'Need manually enter desired number of rows per " page " ' (or " group " ) in D1 and desired number of columns in D2 ' do your own fitting of " groups " onto each excel page Dim x(65537, 1) As String, a, b, c, epp, np As Integer Dim nr, ns, n As Variant, u 'clear contents of range C3:IV65536 Range(Cells(3, 3), Cells(65536, 256)).ClearContents nr = Range( " D1 " ) 'nr is nrows ns = Range( " D2 " ) 'ns is ncols epp = nr * ns n = 1 Do While IsEmpty(Cells(n, 1)) = False x(n - 1, 1) = Cells(n, 1) n = n + 1 If n = 65537 Then Exit Do Loop np = Int(n / epp) + 1 For c = 1 To np Cells((c - 1) * (nr + 1) + 3, 3) = " page (group) " & c & " of " & np For a = 1 To nr For b = 1 To ns u = a + (b - 1) * nr + (c - 1) * epp If u > n Then GoTo sux Cells(a + (c - 1) * (nr + 1) + 3, b + 2) = x(u, 1) Next b sux: Next a, c Cells(1, 1) = n & " entries on initial list " End Sub	parry (27)
100265	2003-12-17 22:05:00	oops didnt read ths bit so fair enough.... The code would work just as well if it were x(n).	parry (27)
100266	2003-12-17 23:12:00	Hi Parry, Thanks for your comments. Much appreciated! Couple more points. 1. I didn't bother sorting out the end effect if one has 256 squared entries (in excel the maximum allowable number of rows per worksheet, 65536, is the square of the maximum allowable number of columns, 256). I could have squeezed in one more entry by starting entries at cell(A1) rather than A2. But for n=65536 entries there is no n+1 allowed as you point out. There's a variety of counting methods to handle this if it were ever necessary. 2. If either strings or numbers are in the original list then set dim x() as variant. I didn't do this because variants use more memory. Also nr and ns are integers, and n is long, so I don't see why you call these variants. 3. To make this comparable with Russell D's macro, can put nr=55 and ns=9 and scrub the range D1 and D2 bit. I just wanted to make it more easily modified. Also I put in the page numbers just to help assess the output - again optional. 4. The Cells(a + (c - 1) * (nr + 1) + 3, b + 2) = x(u, 1) is just a way of selecting entries from the original list and putting them into the correct cells of the new outputted array. 5. To sort out 65550 entries into pages with 55 rows and 9 columns took my computer just 12 seconds also. Using Russell D's macro, which also seems to work fine, took me 78 seconds to do the same job although without the pages being numbered. Still a lot better than doing the job manually. 6. On your 'clear contents of range C3:IV65536 Range(Cells(3, 3), Cells(65536, 256)).ClearContents it’s maybe a bit shorter to just use Range("C3:IV65536").clearcontents	rugila (214)
100267	2003-12-17 23:41:00	Thanks for taking the time to comment Rugila. > 2. If either strings or numbers are in the original > list then set dim x() as variant. I didn't do this > because variants use more memory. Also nr and ns are > integers, and n is long, so I don't see why you call > these variants. err long story. The second row of Dims werent in your original code and as I have Option Explicit on my modules it kept asking for a declaration so I just added the missing Dims as the errors occured with no type (ie variants). The n has As Variant because I couldnt work out what that error was about type mismatch (I though it may be this variable at first) and is basically a leftover after I changed this variable to a couple of other types. I could have tried to work out what the variable types should have been for them all but it didnt seem worth it. > 4. The Cells(a + (c - 1) * (nr + 1) + 3, b + 2) = > x(u, 1) is just a way of selecting entries from the > original list and putting them into the correct cells > of the new outputted array. Yes I thought you were trfering to another array, its just theres a few variables being knocked about. It takes a bit of thinking to understand other peoples code is all. I think I will have to step through the code to satisfy myself I understand it properly. I just cant be arsed at the moment :-) > 5. To sort out 65550 entries into pages with 55 rows > and 9 columns took my computer just 12 seconds also. > Using Russell D's macro, which also seems to work > fine, took me 78 seconds to do the same job although > without the pages being numbered. Still a lot better > than doing the job manually. Cool. Just over a minute is not bad either but you can certainly see how fast arrays are compared to other operations. > 6. On your > 'clear contents of range C3:IV65536 > Range(Cells(3, 3), Cells(65536, 256)).ClearContents > it’s maybe a bit shorter to just use > Range("C3:IV65536").clearcontents Yeah I know. I seem to type ranges in different ways all the time - just bored I guess. I sometimes use ranges like [c3:iv65536].clearcontents as well to mix it up.	parry (27)
1 2 3 4 5