Re: [libreoffice-users] Searching for ”empty” cell (LibreOffice BASIC macro)

2012/7/20 Andrew Douglas Pitonyak <andrew@pitonyak.org>:
> You may also try using the cell range and calling GetDataArray() to get all
> of the data including numbers and strings. If you are simply running through
> about 2000 cells, this should be very fast, especially if you know that the
> cells are filled sequentially because then you can use a binary type search
> rather than a sequential search.

Yes, that's a nice workaround and it's probably what I'm going to do. Thanks.
> In AndrewMacro.odt I have a section on searching Calc comparing three
> different methods. It might be useful.

Yes, I've read it many times; it's been around for a couple of years
now in different versions, I'm not sure I have the latest one though,
but it seems relevant enough. You wrote about three cases, first going
cell by cell, which takes like forever (was it a bit over 1800 system
ticks?), then by storing into an array, took 54 ticks, as far as I
remember, and at last using the built in search function, which took
34 ticks or so.

Although my problem is probably solved by this, I still want to know
if that behaviour I experienced with the .SearchType thing, is it a
bug? Because manually using the dialogue that pops up at Ctrl+h
(Ctrl+f in earlier versions) gives the result I want, but I can't
implement it into a macro using the ”createSearchDescriptor()” thing,
as it seems.


Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

> On 07/20/2012 03:31 AM, Fernand Vanrie wrote:
> >  Johnny,
> > I suppose you have to run the check 2 Times, first SearchType = 0 here we
> > find if there is a formula use  "."
> > then SearchType 1 on the found area
> >
> > the SearchString has a different meaning , depending on the .SearchType
> > = 1 then the SearchString is the is the result of the formula or the value
> > content
> > = 0 then the SearchString is the formula string
> >
> > hope it helps
> >
> > Fernand
> > > Just can't figure it out. I have a column of 2000 formulas and values.
> > > Right now, A1:A1620 contains values, and A1621:A2000 contains
> > > formulas. The formulas in A1621:A2000, at the moment, return empty
> > > strings, all of them, so it looks like only the 1620 first rows
> > > contains data.
> > > So the formulas looks something like =IF(this and that;"";something
> > > else) (but a bit more complicated). The point is that if I input
> > > something on a ”new” row in one of the other columns, the A column
> > > shall, in some cases, display something.
> > >
> > > Okay, that's what the spreadsheet looks like, roughly.
> > >
> > > Now I use a couple of macros to do things for me a lot faster than I
> > > could ever do myself. One small part of a new macro I'm trying to
> > > write needs to search for the first ”empty” row, which means the first
> > > row where the A column contains a formula that returns an empty
> > > string.
> > >
> > > Here's what I tried:
> > > Function FindCurrentRow(Sheet As Object) As Integer
> > >     Dim SearchDescriptor As Object
> > >     SearchDescriptor=Sheet.createSearchDescriptor()
> > >     With SearchDescriptor
> > >         .SearchByRow=False ' I want to search by column, starting at A.
> > >         .SearchRegularExpression=False
> > >         .SearchString=""
> > >         .SearchType=1 ' 0=Search in formulae, 1=Search values.
> > >     End With
> > >
> > >     Dim Found As Object
> > >     Found=Sheet.findFirst(SearchDescriptor)
> > >     FindCurrentRow=Found.getCellAddress().Row
> > > End Function
> > >
> > > In this example I expect the function to return 1620 (which is the row
> > > address for the cell A1621). Instead 2000 is returned, so for some
> > > reason, when my cell formula returns "", that doesn't seem to be the
> > > same as .SearchString="".
> > > I also tried different values of .SearchValue, still with the same
> > > result: 2000 instead of 1620. So it only finds the first cell in the A
> > > column that is REALLY empty – no formula, no value.
> > >
> > > To me this seems like a bug, but for someone else, hopefully, it might
> > > seem like I'm just stupid, so feel free to call me stupid and, more
> > > important, tell me what I'm doing wrong and how I should do instead…
> > >
> > >
> > >
> > > Kind regards
> > >
> > > Johnny Rosenberg
> > > ジョニー・ローゼンバーグ
>
> --
> Andrew Pitonyak
> My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
> Info:  http://www.pitonyak.org/oo.php
>
>
>
>
>
> --
> For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted