2012/7/20 MiguelAngel <mari...@miguelangel.mobi>: > El 20/07/12 21:33, Johnny Rosenberg escribió: > >> 2012/7/20 Johnny Rosenberg <gurus.knu...@gmail.com>: >>> >>> 2012/7/20 Fernand Vanrie <s...@pmgroup.be>: >>>> >>>> Op 20/07/2012 13:31, Johnny Rosenberg schreef: >>>> >>>>> 2012/7/20 Fernand Vanrie<s...@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 expressions >>> >>> >>> I 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. >> >> >> Ok, I'd just better give up, I guess. LibreOffice seems to be way too >> slow for the things I do (I am surprised that I seem to be the only >> one who actually do something with spreadsheets, except simple tables >> of a few rows and columns, doing close to nothing, more or less). Many >> years ago I did similar things in Excel. It crashed now and then, but >> it was fast, at least. Things that took maybe one second to execute in >> Excel seems to take hours in LibreOffice these days. I don't know >> exactly how long they take, since I can never wait for more than a >> couple of minutes before I restart it. It's very frustrating, when >> testing new stuff. Simple developing a spreadsheet (something that you >> shouldn't do, obviously) takes days instead of minutes. >> >> I am not sure where things go slow. My current spreadsheet isn't >> particularly big. There are currently about 2000 rows in 5 columns >> containing formulas and there is one column with about 3300 rows of >> formulas. Not that complicated formulas, mostly nested IF's. One of >> the 2000 row columns use a custom formula (written in LibreOffice >> BASIC), not very complicated: It reads the values from two cells and >> returns a string value depending on the input values. A few IF's, >> that's all. >> I also use conditional formatting in all of the cells, approximately >> 3300+6×2000=15300 cells. It's not like millions of cells or something. >> >> What is likely the most time consuming part in this case? My own cell >> formula in a couple of hundred cells? The conditional formatted cells? >> All those longer formulas with nested IF's? >> >> Anyway, back to the subject… The thing I wrote about that regular >> expression above was not quite accurate. I am not sure what to think >> any more, it all feels like a mess, kind of. It seems like cells >> containing a number also is found with the search descriptor above. I >> am not sure why this is considered a proper behaviour, but maybe it >> is. >> >> Well, I am not sure what more to say, feel quite confused, to say the >> least… >> >> What are you other guys using a spreadsheet for? It seems like >> whatever I try to do, it all turns out as an extremely slow confusing >> mess. >> >> >> Sorry for being such a jerk. >> >> >> Kind regards >> >> Johnny Rosenberg >> ジョニー・ローゼンバーグ >> >>> >>> >>> Kind regards >>> >>> Johnny Rosenberg >>> ジョニー・ローゼンバーグ >>> >>>> >>>>>> then SearchType 1 on the found area >>>>> >>>>> >>>>> Area? 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 value >>>> >>>>>> 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 >>>>> >>>>> >>>>> Okay, that's not very well designed… at least not in my opinion. >>>>> >>>>>> = 0 then the SearchString is the formula string >>>>> >>>>> >>>>> Meaning what if a cell contains only a value? >>>>>> >>>>>> >>>>>> hope it helps >>>>> >>>>> >>>>> I 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 >>>>> ジョニー・ローゼンバーグ >>>>> >>>>>> 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 Rosenb erg >>>>>>> ジョニー・ローゼンバーグ >>>>>>> >>>>>> >>>>>> -- >>>>>> 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 >> >> > > Johnny, > maybe this solved bug that I had reported, > https://bugs.freedesktop.org/show_bug.cgi?id=49764 > now fixed (I hope) can give some light about the slowness, see the Markus > comments. > > Regards. > Miguel Ángel. > Maybe that adds to the slowness I had already, but my spreadsheet was slow with 3.3.4 too. I upgraded to 3.5.5 earlier this week, and I'm not sure it is slower now, but it is certainly not faster. But I'll install 3.6 when I have some time left and see if there is any difference.
There are so many other things that are very slow as well, for example auto-filter and diagrams. I have one diagram in another spreadsheet. It use data from 365 rows (one for each day of a year) and something like 5-10 columns. If I want to change the size, for example, of that diagram, I need to wait for several seconds, maybe 30 or so, before anything happens after I double clicked the diagram. I also made a similar one for a month, that is 31 rows and about 5-10 columns. It's faster than the year one of course, but still too slow for most situations. If it was millions of cells I would understand it, but 365? That's nothing! Right? Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- 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