| 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 | ||
| 100253 | 2003-12-17 05:26:00 | In case you werent aware of the above I should also mention that when using a variable for a row you should declare it as Long as Integers only go up to 32,767 while the # rows could be up to 65,536. | parry (27) | ||
| 100254 | 2003-12-17 07:58:00 | Hmmm...... I'm a sort of .."if it works then hey - that's nice..." guy - if it don't work I'll massage it till it does. I find these variables a bit confusing - perhaps I should declare "Option Explicit" at the start and it would force correct declarations in the subsequent code!. Thanks for your comments - much appreciated. Cheers, Russell |
Russell D (18) | ||
| 100255 | 2003-12-17 08:35:00 | Your welcome :-) I dont trust myself so I always have Option Explicit. You can have this statement automatically added to new modules (wont fix existing modules) by selecting the 'Require Variable Declaration' option under VBE Tools-Options-Editor tab. Bye. |
parry (27) | ||
| 100256 | 2003-12-17 09:43:00 | > As an aside you sound as though your manually > checking the file names against something. Im not > sure what your doing but couldnt this be automated? Hi Parry, this spreadsheet isn't filenames (the one from last year was though). This is a sheet of unique id numbers. We're just in the process of switching our databases over from an old Unix setup to a new database using ArcSDE (a database engine) to connect to a SQL database... Anyway, there are a few teething problems, and one of them is tying the new database in with another completely different database system (the main database is supposed to pull these unique ID numbers from the second database). Basically the two aren't talking to each other how we want them to, so while external people are working on building us a custom app to do the database automation, we're going to have to use a manual list of numbers printed out that we'll cross off as we use them. I hope that makes sense, cause it doesn't really to me ;) :p (well okay, I'm sad to admit, it actually does make sense :() Thanks for all your help :D Mike. |
Mike (15) | ||
| 100257 | 2003-12-17 09:44:00 | Much thanks to both Russell and Parry - you two have been outstanding! Mike. |
Mike (15) | ||
| 100258 | 2003-12-17 09:45:00 | Thanks Russell, that did the job :D Mike. |
Mike (15) | ||
| 100259 | 2003-12-17 19:46:00 | '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 Long, a, b, c, epp, np As Integer Range( " K:P " ).ClearContents nr = Range( " D1 " ) 'nr is nrows ns = Range( " D2 " ) 'ns is ncols epp = nr * ns n = 0 Do While IsEmpty(Cells(n + 2, 1)) = False n = n + 1 x(n, 1) = Cells(n + 1, 1) 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 |
rugila (214) | ||
| 100260 | 2003-12-17 20:00:00 | What is "x"? | Russell D (18) | ||
| 100261 | 2003-12-17 20:37:00 | Hi Russell, "x" is a two-dimensional array. For brief info on arrays see here (www.cpearson.com). Hi Rugila I get a runtime err 13 Type Mismatch on... x(n, 1) = Cells(n + 1, 1) I have data in column A and row/col values D1 & D2 |
parry (27) | ||
| 100262 | 2003-12-17 21:07:00 | Ok, Ive found the problem. "X" should be a String not Long. I got a not responding crash now. | parry (27) | ||
| 1 2 3 4 5 | |||||