Re: [libreoffice-users] Searching for ”empty” cell (LibreOffice BASIC macro)

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?

> then SearchType 1 on the found area

Area? You mean what's found when setting ”search all”?

> 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 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