Searching for ”empty” cell (LibreOffice BASIC macro)

classic Classic list List threaded Threaded
23 messages Options
Next » 12
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Searching for ”empty” cell (LibreOffice BASIC macro)

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: [hidden email]
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
Fernand Vanrie Fernand Vanrie
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

  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
> ジョニー・ローゼンバーグ
>


--
For unsubscribe instructions e-mail to: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

2012/7/20 Fernand Vanrie <[hidden email]>:
>  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: [hidden email]
> 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: [hidden email]
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
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

Try my "SpecialCells" extension which selects combinations of cell types:
http://user.services.openoffice.org/en/forum/download/file.php?id=11048
Andrew Pitonyak Andrew Pitonyak
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Fernand Vanrie

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.

In AndrewMacro.odt I have a section on searching Calc comparing three
different methods. It might be useful.

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: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Andreas Säger
2012/7/20 Andreas Säger <[hidden email]>:
> Try my "SpecialCells" extension which selects combinations of cell types:
> http://user.services.openoffice.org/en/forum/download/file.php?id=11048
>

Okay, I didn't install any extensions before, at least not since quite
a along time ago. So I grabbed your file, Tools → Extension → Add…,
the extension is now in the list. Now what…? How do I use it? Can't
find it. Can I see the code?
Sorry for these idiot questions…


Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

>
>
> --
> View this message in context: http://nabble.documentfoundation.org/Searching-for-empty-cell-LibreOffice-BASIC-macro-tp3996513p3996615.html
> Sent from the Users mailing list archive at Nabble.com.
>
> --
> For unsubscribe instructions e-mail to: [hidden email]
> 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: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Andrew Pitonyak
2012/7/20 Andrew Douglas Pitonyak <[hidden email]>:
>
> 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.


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: [hidden email]
> 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: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Johnny Rosenberg
2012/7/20 Johnny Rosenberg <[hidden email]>:

> 2012/7/20 Andreas Säger <[hidden email]>:
>> Try my "SpecialCells" extension which selects combinations of cell types:
>> http://user.services.openoffice.org/en/forum/download/file.php?id=11048
>>
>
> Okay, I didn't install any extensions before, at least not since quite
> a along time ago. So I grabbed your file, Tools → Extension → Add…,
> the extension is now in the list. Now what…? How do I use it? Can't
> find it. Can I see the code?
> Sorry for these idiot questions…

Okay, got it. I needed to restart LibreOffice, then I found it easily.


Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

>
>
> Kind regards
>
> Johnny Rosenberg
> ジョニー・ローゼンバーグ
>
>>
>>
>> --
>> View this message in context: http://nabble.documentfoundation.org/Searching-for-empty-cell-LibreOffice-BASIC-macro-tp3996513p3996615.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>> --
>> For unsubscribe instructions e-mail to: [hidden email]
>> 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: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Johnny Rosenberg
2012/7/20 Johnny Rosenberg <[hidden email]>:

> 2012/7/20 Andrew Douglas Pitonyak <[hidden email]>:
>>
>> 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: [hidden email]
>> 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: [hidden email]
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
Fernand Vanrie Fernand Vanrie
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Johnny Rosenberg
Op 20/07/2012 13:31, Johnny Rosenberg schreef:
> 2012/7/20 Fernand Vanrie<[hidden email]>:
>>   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
>> 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 Rosenberg
>>> ジョニー・ローゼンバーグ
>>>
>>
>> --
>> For unsubscribe instructions e-mail to: [hidden email]
>> 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: [hidden email]
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
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Johnny Rosenberg
As far as I know, the find/replace tool can not find empty strings nor blanks.
The standard filter can. It has an "-- empty --" option for the blanks and one empty entry at the end of the combo box for the empty strings (if any).
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

2012/7/20 Andreas Säger <[hidden email]>:
> As far as I know, the find/replace tool can not find empty strings nor
> blanks.
> The standard filter can. It has an "-- empty --" option for the blanks and
> one empty entry at the end of the combo box for the empty strings (if any).

Can I use that in a macro? How?


Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

>
>
>
> --
> View this message in context: http://nabble.documentfoundation.org/Searching-for-empty-cell-LibreOffice-BASIC-macro-tp3996513p3996716.html
> Sent from the Users mailing list archive at Nabble.com.
>
> --
> For unsubscribe instructions e-mail to: [hidden email]
> 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: [hidden email]
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
Jon Morgan Jon Morgan
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

Please unsubscribe me.

On 7/20/2012 11:56 AM, Johnny Rosenberg wrote:

> 2012/7/20 Andreas Säger <[hidden email]>:
>> As far as I know, the find/replace tool can not find empty strings nor
>> blanks.
>> The standard filter can. It has an "-- empty --" option for the blanks and
>> one empty entry at the end of the combo box for the empty strings (if any).
> Can I use that in a macro? How?
>
>
> Kind regards
>
> Johnny Rosenberg
> ジョニー・ローゼンバーグ
>
>>
>>
>> --
>> View this message in context: http://nabble.documentfoundation.org/Searching-for-empty-cell-LibreOffice-BASIC-macro-tp3996513p3996716.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>> --
>> For unsubscribe instructions e-mail to: [hidden email]
>> 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: [hidden email]
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
Tom Tom
Reply | Threaded
Open this post in threaded view
|

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

Hi :)
Pleas follow the guide at
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
We can't randomly unsubscribe people from this end but you can unsubscribe yourself just by following the instructions
Apols and regards from
Tom :) 



--- On Fri, 20/7/12, Jon Morgan <[hidden email]> wrote:

From: Jon Morgan <[hidden email]>
Subject: Re: [libreoffice-users] Re: Searching for ”empty” cell (LibreOffice BASIC macro)
To: [hidden email], [hidden email]
Date: Friday, 20 July, 2012, 18:28

Please unsubscribe me.

On 7/20/2012 11:56 AM, Johnny Rosenberg wrote:

> 2012/7/20 Andreas Säger <[hidden email]>:
>> As far as I know, the find/replace tool can not find empty strings nor
>> blanks.
>> The standard filter can. It has an "-- empty --" option for the blanks and
>> one empty entry at the end of the combo box for the empty strings (if any).
> Can I use that in a macro? How?
>
>
> Kind regards
>
> Johnny Rosenberg
> ジョニー・ローゼンバーグ
>
>>
>>
>> --
>> View this message in context: http://nabble.documentfoundation.org/Searching-for-empty-cell-LibreOffice-BASIC-macro-tp3996513p3996716.html
>> Sent from the Users mailing list archive at Nabble.com.
>>
>> --
>> For unsubscribe instructions e-mail to: [hidden email]
>> 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: [hidden email]
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: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Fernand Vanrie
2012/7/20 Fernand Vanrie <[hidden email]>:

> Op 20/07/2012 13:31, Johnny Rosenberg schreef:
>
>> 2012/7/20 Fernand Vanrie<[hidden email]>:
>>>
>>>   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.


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 Rosenberg
>>>> ジョニー・ローゼンバーグ
>>>>
>>>
>>> --
>>> For unsubscribe instructions e-mail to: [hidden email]
>>> 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: [hidden email]
> 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: [hidden email]
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
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Johnny Rosenberg
Am 20.07.2012 18:56, Johnny Rosenberg wrote:
> 2012/7/20 Andreas Säger<[hidden email]>:
>> As far as I know, the find/replace tool can not find empty strings nor
>> blanks.
>> The standard filter can. It has an "-- empty --" option for the blanks and
>> one empty entry at the end of the combo box for the empty strings (if any).
>
> Can I use that in a macro? How?
>

Apply such filter manually and inspect the filter descriptor with its
filter fields.


--
For unsubscribe instructions e-mail to: [hidden email]
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
Andreas Säger Andreas Säger
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Andreas Säger
Am 20.07.2012 14:19, Andreas Säger wrote:
> Try my "SpecialCells" extension which selects combinations of cell types:
> http://user.services.openoffice.org/en/forum/download/file.php?id=11048
>
>
>

Done. Snippet recorded by the MRI inspector:

> Sub Snippet(Optional oInitialTarget As Object)
>   Dim oDatabaseRanges As Object
>   Dim oObj_1 As Object
>   Dim oFilterDescriptor As Object
>   Dim oFilterFields As Object
>
>   oDatabaseRanges = oInitialTarget.DatabaseRanges
>   oObj_1 = oDatabaseRanges.getByIndex(0)
>   oFilterDescriptor = oObj_1.getFilterDescriptor()
>
>   oFilterFields = oFilterDescriptor.getFilterFields()
> End Sub


And this is the output for oFilterFields:

> (Name)        (Value Type)             (Value) (AccessMode)
> (0)
> Connection    .sheet.FilterConnection  AND   [ReadWrite]
> Field         long                     0     [ReadWrite]
> Operator      .sheet.FilterOperator    EMPTY [ReadWrite]
> IsNumeric     boolean                  True [ReadWrite]
> NumericValue  double                   0.0   [ReadWrite]
> StringValue   string                   ""   [ReadWrite]
> (1)
> Connection    .sheet.FilterConnection  OR   [ReadWrite]
> Field         long                     0     [ReadWrite]
> Operator      .sheet.FilterOperator    EQUAL [ReadWrite]
> IsNumeric     boolean                  False [ReadWrite]
> NumericValue  double                   0.0   [ReadWrite]
> StringValue   string                   ""   [ReadWrite]

The blank field (0) uses c.s.s.sheet.FilterOperator.EMPTY with any content.
The empty string field (1) uses c.s.s.sheet.FilterOperator.EQUAL with
string content "".


--
For unsubscribe instructions e-mail to: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Johnny Rosenberg
2012/7/20 Johnny Rosenberg <[hidden email]>:

> 2012/7/20 Fernand Vanrie <[hidden email]>:
>> Op 20/07/2012 13:31, Johnny Rosenberg schreef:
>>
>>> 2012/7/20 Fernand Vanrie<[hidden email]>:
>>>>
>>>>   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 Rosenberg
>>>>> ジョニー・ローゼンバーグ
>>>>>
>>>>
>>>> --
>>>> For unsubscribe instructions e-mail to: [hidden email]
>>>> 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: [hidden email]
>> 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: [hidden email]
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 Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Andreas Säger
2012/7/20 Andreas Säger <[hidden email]>:

> Am 20.07.2012 14:19, Andreas Säger wrote:
>>
>> Try my "SpecialCells" extension which selects combinations of cell types:
>> http://user.services.openoffice.org/en/forum/download/file.php?id=11048
>>
>>
>>
>
> Done. Snippet recorded by the MRI inspector:
>
>> Sub Snippet(Optional oInitialTarget As Object)
>>   Dim oDatabaseRanges As Object
>>   Dim oObj_1 As Object
>>   Dim oFilterDescriptor As Object
>>   Dim oFilterFields As Object
>>
>>   oDatabaseRanges = oInitialTarget.DatabaseRanges
>>   oObj_1 = oDatabaseRanges.getByIndex(0)
>>   oFilterDescriptor = oObj_1.getFilterDescriptor()
>>
>>   oFilterFields = oFilterDescriptor.getFilterFields()
>> End Sub
>
>
>
> And this is the output for oFilterFields:
>
>> (Name)        (Value Type)             (Value)  (AccessMode)
>> (0)
>> Connection    .sheet.FilterConnection  AND      [ReadWrite]
>> Field         long                     0        [ReadWrite]
>> Operator      .sheet.FilterOperator    EMPTY    [ReadWrite]
>> IsNumeric     boolean                  True     [ReadWrite]
>> NumericValue  double                   0.0      [ReadWrite]
>> StringValue   string                   ""       [ReadWrite]
>> (1)
>> Connection    .sheet.FilterConnection  OR       [ReadWrite]
>> Field         long                     0        [ReadWrite]
>> Operator      .sheet.FilterOperator    EQUAL    [ReadWrite]
>> IsNumeric     boolean                  False    [ReadWrite]
>> NumericValue  double                   0.0      [ReadWrite]
>> StringValue   string                   ""       [ReadWrite]
>
>
> The blank field (0) uses c.s.s.sheet.FilterOperator.EMPTY with any content.
> The empty string field (1) uses c.s.s.sheet.FilterOperator.EQUAL with string
> content "".

Thanks. I'll take a closer look at it tomorrow. Too tired and confused
right now.


Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

>
>
>
> --
> For unsubscribe instructions e-mail to: [hidden email]
> 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: [hidden email]
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
m.a.riosv m.a.riosv
Reply | Threaded
Open this post in threaded view
|

Re: Searching for ”empty” cell (LibreOffice BASIC macro)

In reply to this post by Johnny Rosenberg
El 20/07/12 21:33, Johnny Rosenberg escribió:

> 2012/7/20 Johnny Rosenberg <[hidden email]>:
>> 2012/7/20 Fernand Vanrie <[hidden email]>:
>>> Op 20/07/2012 13:31, Johnny Rosenberg schreef:
>>>
>>>> 2012/7/20 Fernand Vanrie<[hidden email]>:
>>>>>
>>>>>    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: [hidden email]
>>>>> 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: [hidden email]
>>> 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.

--
For unsubscribe instructions e-mail to: [hidden email]
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
Next » 12