PDA

View Full Version : Excel and filenames again!



Mike
21-11-2002, 08:38 PM
This time I've got 5500+ files listed all in one column, and to print it'll take 195 pages, so I was wanting to cut and paste all the files so they fit accross the page in columns as well (fit about 9 columns per page, but not sure how many cells down the page). All up I think it was going to come to around 20 pages. But it's going to take me all day to cut and paste all the new page-long columns - is there an easier way? :D The excel sheet isn't even for my final product - it's just so I can print it and check off filenames on something else (not near a computer, so I can't just check on the PC everytime).

Does all that make sense?

Instead of having (for example):
file1
file2
file3
file4
file5
file6
file7
file8
file9
file10
file11
file12
I rather want:
file1 file4 file7 file10
file2 file5 file8 file11
file3 file6 file9 file12
Mike.

wuppo
21-11-2002, 09:24 PM
If you have Access, import the list from excel (File/Get External Data). Create a report from the table generated from the import. Goto 'File/Page Setup/Columns' and adjust the number of columns you want in the report.

-=JM=-
22-11-2002, 01:19 AM
Make the boss pay up for a work laptop (you need a way to get work home anyway). hmmm a 2GHz+ P4 with a GF4 2go should do the trick ]:)

Mike
22-11-2002, 09:11 AM
No Access on this machine :(

Mike.

Mike
22-11-2002, 09:12 AM
I work for the Government, JM... that's not going to happen :)

Mike.

wuppo
22-11-2002, 09:28 AM
How about pasting the list into Word and formatting as multicolumn pages?

Mike
22-11-2002, 10:48 AM
>>> How about pasting the list into Word and formatting as multicolumn pages?

That might just work :) thanks Wuppo!

Mike.

Russell D
22-11-2002, 11:09 AM
The following macro will do this.

Copy your file list to cell A1 in new worksheet.

Edit the number of lines per page from 79 to whatever you need.

Sub file_split()
Dim n, m, pl As Integer
Dim rng As Range
'pl = lines per page
pl = 79
m = Round(Range("a1", Range("a1").End(xlDown)).Cells.Count / pl)
Range("a1").Select
n = -1
Do
n = n + 1
ActiveCell.Offset(pl, 0).Select
Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
rng.Select
rng.Cut Range("a1").Offset(0, n + 1)
Range("a1").Offset(0, n + 1).Select
Loop Until n = m - 1
End Sub

HTH

rugila
22-11-2002, 12:44 PM
Or you might try this macro:
List your column of filenames starting in A1. Enter the number of rows you want the new columns to take up in G1 and go to it.


Dim cof As Integer, i As Integer, j As Integer, x As Integer
Dim rown As Integer, coln As Integer
Range("G1") = 3
Range("D1") = "Filenames wanted per row"
rown = Range("G1") 'numbers of file names you want in each row

'cof is count of file names in column 1 starting from row 2 and finishing when get to empty cell
cof = 0
Do While IsEmpty(Cells(cof + 1, 1)) = False
cof = cof + 1
Loop
Cells(1, 2) = cof & " filenames)"

'calculate numbers of colums needed
coln = Int(cof / rown) + 1

'putting nrow file names in each column
For j = 1 To coln
For x = (j - 1) * rown + 1 To j * rown
Cells(x - (j - 1) * rown + 2, j + 2) = Cells(x, 1)
Next x
Next j

End Sub

rugila
22-11-2002, 01:55 PM
Two macros have been suggested to do the job, both seem to work OK.
Just for interest I tried both of them to list a single column of 25000 entries into 250 columns of 100 entries in each. With my machine one macro took 2 minutes, the other took 5 seconds to do the same job.
What does this show? Nothing at all really, but for interest nevertheless.
Mapping 64000 entries columns with 300 entries in each took 16 seconds with one macro, with the other I had time to have my morning coffee.

Graham L
22-11-2002, 04:14 PM
What might work is to Export as comma delimited file, then import it as comma delimited file to a data base with the appropriate number of text fields per record. You might be able to specify a spreadsheet to do it ?thatt way? I don't know.:D

Rude and crude however you do it ... I'd write a little programme in Pascal to do it, myself.

Mike
24-11-2002, 10:31 AM
The file was originally a CSV file from a dir /b *.tif > files.csv - that's how I got all the file names in there to start with :)

But no database program on my PC. I tried Wuppo's method of just exporting into Word and setting it up with a number of columns per page - it worked brilliantly, and I had it all done within around 5-10 seconds (had to wait for Word to open ;))

Thanks people :)

Mike.

Mike
16-12-2003, 03:10 PM
Russel D or Rugila or anybody else who might be able to edit one of the macros, or create a new one ;),

I'm back again with a similar problem, but this time my doing it in Word doesn't work (too many entries!)

I tried both your macros, and they both work, to a point :) They stop when they hit column 256 (IV or something I think). Is it possible to set it to automatically skip back to column A and then continue? Or perhaps go page by page but down (instead of across) the screen? I need the numbers to list down in columns, but one page at a time.

Does that make sense???

Cheers,
Mike.

parry
16-12-2003, 04:48 PM
G'Day this took about 3 secs for 30,000 on my PC. Take a not of how many rows you have and take a guess at the columns (say 9) then run the macro. It will ask you for # rows & cols then create a new sheet called zigzag and place the value in there. Im presuming they are all in column A starting from A1.

Repeat until your happy with width/height to get the max per sheet. All depends on font size etc so Ive made it flexible for you to play around. I havent bothered to put any error checking so if ZigZag sheet already exists the code will bomb so just kill the sheet if you want to run it again with different parameters.



Sub ZigZag()
'By Parry 16/12/03
Dim Rng As Range, MyArray() As String, Arr1
Dim NumCols As Integer, NumRows As Long, NumFiles As Long
Dim R As Long, C As Integer, J As Long, Result As Range

'Set range holding file names
Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row)

'Find # rows & columns wide for the print
NumCols = Application.InputBox(prompt:="Enter # columns", _
Title:="Width of Print Job", Type:=3)
NumRows = Application.InputBox(prompt:="Enter # rows", _
Title:="Height of Print Job", Type:=3)

'Determine array dimensions
NumFiles = Rng.Cells.Count
If NumFiles Mod NumCols = 0 Then
Arr1 = NumFiles / NumCols
Else
Arr1 = NumFiles / NumCols + 1
End If
ReDim MyArray(1 To Arr1, 1 To NumCols)

For R = 1 To Arr1
For C = 1 To NumCols
J = J + 1
MyArray(R, C) = Cells(J, 1).Value
Next C
Next R

'Add new Sheet to store the result
Sheets.Add
Sheets(1).Name = "ZigZag"
Set Result = Sheets(1).Range(Cells(1, 1), Cells(Arr1, NumCols))
Result = MyArray
End Sub


hth

Russell D
16-12-2003, 04:55 PM
Mike,

It looks like you may need to split the list of files between a couple of worsheets before running the print macro to avoid the 256 column limit.
For example, if there are 9 columns and 79 rows per page then the maximum number of whole pages per worksheet is 256/9 = 28. This equates to 9x28x79 = 19908 files per worksheet.
If you cut all rows below row 19908 and paste them to another worksheet at cell A1 - if there are files in rows below 19908 then cut these and paste to a further sheet.
Repeat until there are no more entries below 19908.
Then run my previous macro on each sheet in turn.
Substitute your actual rows per page and columns per page in the above equations to determine the row number to cut and paste from - then edit the number 79 in the macro to suit the actual rows per page.

Hope this makes sense.

Mike
16-12-2003, 05:36 PM
Thanks Parry,

I ran your macro, however after a couple of seconds I get an error message that reads "400" :) I get a new worksheet called ZigZage, but it's empty.

Mike.

Mike
16-12-2003, 05:41 PM
> worksheet called ZigZage, but it's empty.

ZigZag :)

Mike.

Mike
16-12-2003, 05:51 PM
> Thanks Parry,
>
> I ran your macro, however after a couple of seconds I
> get an error message that reads "400" :) I get a new
> worksheet called ZigZage, but it's empty.
>
> Mike.

Debugging it, it gives me an error when I hit the second to last line "Result = MyArray (or it could possibly be stopping on the line before that?):

Run-time error '1004':
Application-defined or object-defined error

Mike.

parry
16-12-2003, 06:08 PM
Weird, as it worked without a hitch for me. Is the sheet that has the file names the active sheet when you run the code? It needs to be. Plus do you have file names in cells A1 downwards? Im not sure what err 400 is - whats the description?

Heres an update to kill the ZigZag sheet if it exists & some rudimentary checking.

Sub ZigZag()
'By Parry 16/12/03
Dim Rng As Range, MyArray() As String, Arr1
Dim NumCols As Integer, NumRows As Long, NumFiles As Long
Dim R As Long, C As Integer, J As Long, Result As Range
Dim Sh As Worksheet, Exist As Boolean

On Error GoTo ErrHand

'Check range
If Range("A1").Value = "" Then
MsgBox "You must have a value in cell A1!"
Exit Sub
End If

'Set range holding file names
Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row)

'Find # rows & columns wide for the print
NumCols = Application.InputBox(prompt:="Enter # columns", _
Title:="Width of Print Job", Type:=3)
NumRows = Application.InputBox(prompt:="Enter # rows", _
Title:="Height of Print Job", Type:=3)

'Determine array dimensions
NumFiles = Rng.Cells.Count
If NumFiles Mod NumCols = 0 Then
Arr1 = NumFiles / NumCols
Else
Arr1 = NumFiles / NumCols + 1
End If
ReDim MyArray(1 To Arr1, 1 To NumCols)

For R = 1 To Arr1
For C = 1 To NumCols
J = J + 1
MyArray(R, C) = Cells(J, 1).Value
Next C
Next R

'Add new Sheet to store the result
On Error Resume Next
Set Sh = Worksheets("ZigZag")
If Error Then
Application.DisplayAlerts = False
Sheets("ZigZag").Delete
Application.DisplayAlerts = True
Err.Clear
End If

Sheets.Add
Sheets(1).Name = "ZigZag"

Set Result = Sheets("ZigZag").Range(Cells(1, 1), Cells(Arr1, NumCols))
Result = MyArray

Exit Sub

ErrHand:
MsgBox "Error # " & Err.Number & vbLf & Err.Description
Exit Sub

End Sub

parry
16-12-2003, 06:14 PM
Debugging it, it gives me an error when I hit the second to last line "Result = MyArray (or it could possibly be stopping on the line before that?):

Run-time error '1004':
Application-defined or object-defined error

Mike.


Ok, that sounds like theres nothing in the array or the dimensions of the range and the array dont match. This would probably happen if the active sheet is not the one with the file names or theres nothing in Column A.

When you enter the number of rows this should match the number of filenames you have.

Let me know how you get on.

Mike
16-12-2003, 08:48 PM
Hi Parry,

The active sheet is the one with the file names in it. What was happening was because I'd attached the macro to the sheet rather than the workbook - now that I've attached it to the workbook it seems to work... mostly :)

On a few records it works fine. But when I try it on a full set of records (60000+) it only returns the first row (set to the number of columns I originally asked for).

Thanks for trying though :) It's not too important that I get this working, as I could just use Russell's idea of pasting however many thousand on seperate sheets :D

Mike.

parry
16-12-2003, 09:41 PM
Hi Mike, sorry I see I made a couple of mistakes with the code. The major failure being to account for 65536 row being occupied so hopefully this will work better.

I placed sample data in column A from row 1 to 65536 which is the row limit and it worked OK. Took about 14 secs which isnt too bad considering the amount of data. Result using 9 columns is 7282 rows or 270 printed sheets if you want to kill a small Brazilian forest :-)

Sub ZigZag()
'By Parry 16/12/03
Dim Rng As Range, MyArray() As String, Arr1
Dim NumCols As Integer, NumRows As Long, NumFiles As Long
Dim R As Long, C As Integer, J As Long, Result As Range
Dim Sh As Worksheet

On Error GoTo ErrHand

'Check range
If Range("A1").Value = "" Then
MsgBox "You must have a value in cell A1!"
Exit Sub
End If

'Set range holding file names
If Range("a65536") <> "" Then 'Account for 65536 rows
Set Rng = Range("A1:A65536")
Else
Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row)
End If

'Find # rows & columns wide for the print
NumCols = Application.InputBox(prompt:="Enter # columns", _
Title:="Width of Print Job", Type:=3)
NumRows = Application.InputBox(prompt:="Enter # rows", _
Title:="Height of Print Job", Type:=3)

'Determine array dimensions
NumFiles = Rng.Cells.Count
If NumFiles Mod NumCols = 0 Then
Arr1 = NumFiles / NumCols
Else
Arr1 = Int(NumFiles / NumCols) + 1 'Duh forgot to kill decimals
End If
ReDim MyArray(1 To Arr1, 1 To NumCols)

For R = 1 To Arr1
For C = 1 To NumCols
J = J + 1
MyArray(R, C) = Cells(J, 1).Value
If J = 65536 Then Exit For 'stop if at 65536 row
Next C
Next R

'Add new Sheet to store the result
On Error Resume Next
Set Sh = Worksheets("ZigZag")
If Error Then
Application.DisplayAlerts = False
Sheets("ZigZag").Delete
Application.DisplayAlerts = True
Err.Clear
End If

Sheets.Add
Sheets(1).Name = "ZigZag"

Set Result = Sheets("ZigZag").Range(Cells(1, 1), Cells(Arr1, NumCols))
Result = MyArray

Exit Sub

ErrHand:
MsgBox "Error # " & Err.Number & vbLf & Err.Description
Exit Sub

End Sub

Mike
16-12-2003, 09:59 PM
Well done Parry, it worked :) Now I've crashed Excel though :p nevermind, I'll rerun it and then NOT convert everything back to numbers :D

Just a question - is it possible to go down the page, and then across? EG I want to to print something like 55 lines per page, so it goes from 1 to 55, then column b 1 to 55, across the 9 columns, then jumps to A56 and continues... and so on?

Or is that a lot harder to do?

I really want to learn this VBA type stuff now! :D

Mike.

parry
16-12-2003, 10:57 PM
Yep most things are possible :-)

Im not sure I exactly get what you mean. Reading the data from your sheet is easier to do in long lines - so reading all down column A then all down column b is easier than reading 55 rows in column A, then 55 rows in Column B then the next 55 in column A etc. Or do you mean how many columns in the ZigZag sheet?

The issue isnt really looping through it but trying to understand what can go wrong because of the limitations of the number of rows in Excel.
A database is far easier for something like this because there are no row limits like you strike in Excel but youve already mentioned thats not an option.

Russell D
16-12-2003, 11:05 PM
How many file names do you have and how may characters does the largest file name have, and what font size do you want the to print-out to have.

Mike
17-12-2003, 08:26 AM
> Yep most things are possible :-)
> Im not sure I exactly get what you mean. Reading the
> data from your sheet is easier to do in long lines -
> so reading all down column A then all down column b
> is easier than reading 55 rows in column A, then 55
> rows in Column B then the next 55 in column A etc. Or
> do you mean how many columns in the ZigZag sheet?

I mean that its easier to read columns down then across on the printed page. So going by roughly 55 rows per page, I want to be able to read down the first column, then down the next etc. then after the 9 or so columns I turn the page and I start again reading down the columns. Does that make more sense now?

> The issue isnt really looping through it but trying
> to understand what can go wrong because of the
> limitations of the number of rows in Excel.
> A database is far easier for something like this
> because there are no row limits like you strike in
> Excel but youve already mentioned thats not an
> option.

There wouldn't be any more rows in Excel - I'd just like to be able to read down the page rather than left to right; but still page by page :D

Am I asking too much?

Mike.

Mike
17-12-2003, 08:28 AM
> How many file names do you have and how may

That's unknown at this stage... they're not filenames this time, they're just numbers - non-sequential numbers too some of the time.

> characters does the largest file name have, and what

probably 7 digits.

> font size do you want the to print-out to have.

Just the standard excel setup - I think I get about 55 rows and 9 columns per a4 page... plus/minus some maybe.

Mike.

parry
17-12-2003, 10:36 AM
> I mean that its easier to read columns down then
> across on the printed page. So going by roughly 55
> rows per page, I want to be able to read down the
> first column, then down the next etc. then after the
> 9 or so columns I turn the page and I start again
> reading down the columns. Does that make more sense
> now?

Click, yes I understand. :D

I would need to think about the best way to do this because of the speed issue. My previous code used arrays which are far quicker at writing the information to a sheet than from one range to another. Arrays by nature read left to right then down so writing to a new sheet as per my previous code fits in nicely.

Your scenario is a bit more complicated so I need to understand how to get this into an array properly because you can get some erratic results when it comes time to writing the data from the array if the values have not been place in the array in the correct order.

I'll have a look at it but I wont spend too much time on it at this stage as Russell seems to have some ideas and may have a cleaner solution for you.

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?

Russell D
17-12-2003, 12:55 PM
The following macro will take the data in column A and align it sequentially 55 rows down and 9 columns across, then repeat below at row 56 for the next 55 rows and 9 columns across and repeat until all the data is arranged.
If this sheet is then printed, the data on the pages will be sequential as you have requested.

Edit the "pl" and "pc" values to suit your page row and column sizes.

HTH

Sub align_data()
Dim n, m, a, b, c, pl, pc As Integer
Dim rng As Range
'pl = lines per page
pl = 55
'pc = columns per page
pc = 9
m = Range("a1", Range("a1").End(xlDown)).Cells.Count / (pc * pl)
'c = number of entries
c = Range("a1", Range("a1").End(xlDown)).Cells.Count
'p = number of pages
p = Application.WorksheetFunction.RoundUp(m, 0)
b = 0
For b = 0 To p
Range("a1").Offset(pl * b, 0).Select
n = -1
For a = 1 To pc - 1
n = n + 1
ActiveCell.Offset(pl, 0).Select
Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
rng.Select
If rng.Cells.Count > c Then
Exit Sub
End If
rng.Cut Range("a1").Offset(pl * b, n + 1)
Range("a1").Offset(pl * b, n + 1).Select
Next a
ActiveCell.Offset(pl, 0).Select
Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
rng.Select
rng.Cut Range("a1").Offset(pl * (b + 1), 0)
Next b
End Sub

parry
17-12-2003, 06:12 PM
Well done Russell it worked for me. :-D

I hope you dont mind me mentioning this but in your declarations you have ...
Dim n, m, a, b, c, pl, pc As Integer
then later...
p = Application.WorksheetFunction.RoundUp(m, 0)

Are you wanting n,m,a,b,c & pl to be Integers? They are actually variants unless you declare a data type explicitly, so will include decimals when you divide etc.

You may already know but if you declare multiple variables on the same line in VBA then they all require a data type assigned explicitly with the default being Vaiant if you havent assigned a type.

If you wanted Integers you wuld have to do this (a pain I know)...
Dim n as Integer, m as Integer, a as Integer etc etc...

Apologies if Im out of line but thought I should mention it.

Cheers :-)

parry
17-12-2003, 06:26 PM
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.

Russell D
17-12-2003, 08:58 PM
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

parry
17-12-2003, 09:35 PM
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.

Mike
17-12-2003, 10:43 PM
> 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
17-12-2003, 10:44 PM
Much thanks to both Russell and Parry - you two have been outstanding!

Mike.

Mike
17-12-2003, 10:45 PM
Thanks Russell, that did the job :D

Mike.

rugila
18-12-2003, 08:46 AM
'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

Russell D
18-12-2003, 09:00 AM
What is "x"?

parry
18-12-2003, 09:37 AM
Hi Russell, "x" is a two-dimensional array. For brief info on arrays see here (http://www.cpearson.com/excel/returnin.htm).

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
18-12-2003, 10:07 AM
Ok, Ive found the problem. "X" should be a String not Long. I got a not responding crash now.

rugila
18-12-2003, 10:19 AM
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.

parry
18-12-2003, 10:53 AM
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
18-12-2003, 11:05 AM
oops didnt read ths bit so fair enough....
The code would work just as well if it were x(n).

rugila
18-12-2003, 12:12 PM
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

parry
18-12-2003, 12:41 PM
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.