Hello Andrew, thanks for your reply. In the meanwhile I made some progress (?) but I am stuck on something:
I "discovered" that I could use this formula to do exactly what I need: =INDEX(myTable; MATCH(LARGE(myValuesColumn;rank);myValuesColumn;0); theColumnOfMyTableIWant) where LARGE returns the n-th biggest value in a vector (so I just have to set rank from 1 to 5) I have also discovered by trial and error that by using the .setFormula( aString ) method on a calc cell, I can actually call the above calc function in a macro. What I do not know is if this is the correct way to do it. (although you may argue that as long as it works it must be correct). So my new question is: What is the best way to invoke a calc function (e.g. LARGE, INDEX, MATCH) from a macro? are there some tricks one should know? Cheers, Michele On 05/01/06, Andrew Douglas Pitonyak <[EMAIL PROTECTED]> wrote: > > Michele Zarri wrote: > > >Hello All, > > > >First of all I would like to clarify that I know I should really use base > to > >do what I want to do, but I am also too lazy to learn so I would like to > >keep using calc... sorry! > > > >Ok, here we go with my problem. > >I have a table with some hundreds of entries one of the columns being a > >numerical value. > >What I would like to do is to *use a macro* to find out the top 5 values > and > >return the row number of those entries (which I will use for some > processing > >later on). > >Of course, the brute force such as sticking all the values in an array, > sort > >it and so on does the job, but my feeling is that there must be a clever > way > >to do it. I noticed for example that if I enable the auto filtering on > the > >table I can highlight the top 10 values which is similar if not exactly > to > >what I need. > > > >I was also thinking of using the built in function DMAX, but > >a) how can I invoke it in a macro? > >b) how can I get the second, third, fourth and fifth value? > > > >Anyway, I hope I have been clear in explaining my problem, if not let me > >know. > > > > > I am not a database expert and I am too lazy to lookup the precise > syntax, but.... Regardless, I would use SQL. > > The SQL would likely look something like this: > > select * from TABLE_NAME order by COLUMN_NAME desc where rownum < 6 > > I forget the correct syntax for limiting the number of rows... But, that > is how I would do it.... Then, the macro has to do nothing but collect > the data. The tricky part is figuring out which row number. Then again, > the important thing is NOT that you have the row number, but that you > have the primary key value so that you can easily get the values back.... > > > > > -- > Andrew Pitonyak > My Macro Document: http://www.pitonyak.org/AndrewMacro.odt > My Book: http://www.hentzenwerke.com/catalog/oome.htm > Info: http://www.pitonyak.org/oo.php > See Also: http://documentation.openoffice.org/HOW_TO/index.html > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
