2012/7/20 Johnny Rosenberg <gurus.knu...@gmail.com>: > 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.
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 >>> >>> 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
