2012/7/20 Johnny Rosenberg <gurus.knugum@gmail.com>:
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 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+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
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.