2012/7/20 Andrew Douglas Pitonyak <and...@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+h...@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+h...@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
