2012/7/20 Johnny Rosenberg <gurus.knugum@gmail.com>:
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.
Correction: When using the Ctrl+h dialogue (Search/Replace), I can't search for an empty string at all, since the Search button is disabled until I type something into the ”Search for” field. Using regular expressions, searching for ^$, finds nothing at all. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ
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 FernandJust 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