Johnny, found this stuf on a French forum: Sub PysTests dim PysSel as object, FormulaRetour, EmptyRetour as object dim PysFlagPysFlag = com.sun.star.sheet.FormulaResult.VALUE + com.sun.star.sheet.FormulaResult.STRING + com.sun.star.sheet.FormulaResult.ERROR
PysSel = thiscomponent.currentSelection FormulaRetour = PysSel.queryFormulaCells(PysFlag) xray FormulaRetour EmptyRetour = PysSel.queryEmptyCells(PysFlag) xray EmptyRetour End Sub hope it helps also :-)
2012/7/20 Fernand Vanrie<sos@pmgroup.be>:Op 20/07/2012 13:31, Johnny Rosenberg schreef:2012/7/20 Fernand Vanrie<sos@pmgroup.be>:Johnny, I suppose you have to run the check 2 Times, first SearchType = 0 here we find if there is a formula use "."What do you mean? Use "." as the search string? Regular expressions set to what? True?yep looking for not empty you need regular expressionsI did some brutal experimenting and after hundreds of swearwords (the damned crap freeze all the time) I actually found something. Have a look at this descriptor: With SearchDescriptor .SearchByRow=False .SearchRegularExpression=True .SearchString="^[^.]$" .SearchType=1 ' Search values. End With First it didn't work as I expected, but it found another cell in another column. That cell has a semi complicated formula with nested IF's and in this case it returns F2+STYLE("Hide"). F2 in this case is an empty cell, so now I changed the formula in the cell I wanted to find, by just replacing the two quotes ("") with F2, and now it finds the cell! So "" is not good enough for making a cell empty, but I can reference to a cell that actually is empty! So what I need to do now, is to change all the formulas in one column, and the search descriptor above will work, as it seems! At least I'll try that. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグthen SearchType 1 on the found areaArea? You mean what's found when setting ”search all”?no you can define the "erea" (cells) you want to searched so when you found that 1 cell has a formula, you need to check only this cell on a valuethe SearchString has a different meaning , depending on the .SearchType = 1 then the SearchString is the is the result of the formula or the value contentOkay, that's not very well designed… at least not in my opinion.= 0 then the SearchString is the formula stringMeaning what if a cell contains only a value?hope it helpsI don't know, will do some tests later, but it certainly feels confusing at the moment, because when I use the Search/Replace dialogue, it doesn't work like that at all. Selecting ”Values” in the dialogue give me the result I want, it finds the first cell with an empty value (in this case a cell with a formula that returns that empty value – ""). So one question that comes to my mind is why the LibreOffice Basic Search doesn't work the same way as the dialogue. Maybe it's just fun to confuse the users… As I said, I will do some more tests and come back here later. Thanks for replying. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ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 ジョニー・ローゼンバーグ-- 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
-- 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