OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Is there a way to tell if a cell has nothing in it?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
SomeoneHere
OOo Enthusiast
OOo Enthusiast


Joined: 28 Oct 2005
Posts: 116
Location: Boston, Massachusetts USA

PostPosted: Mon Nov 21, 2005 10:05 am    Post subject: Is there a way to tell if a cell has nothing in it? Reply with quote

I know this is another basic question but if anyone can help it would be great. I am writing a macro for calc and I want the user to be able to see if he or she is going to overwrite something. So basically I am just writing an if statement on whether or not there is anything in a certain cell. I was wondering if anyone new what statement to use for the if statement.
I tried the isEmpty() but I am not sure what that returns. Here is the statement any help would be greatly appreciated.
Code:

if(IsEmpty(Sheet.getCellbyPosition(x+6, found.celladdress.row())))then
                  Sheet.getCellbyPosition(x+6, found.celladdress.row()).setvalue( CasePrice )
               Sheet.getCellbyPosition(x+7, found.celladdress.row()).setvalue( BottlePrice )
               else        
                msgbox("Something here")
Back to top
View user's profile Send private message
ms777
Super User
Super User


Joined: 07 Feb 2004
Posts: 1355

PostPosted: Mon Nov 21, 2005 10:54 am    Post subject: Reply with quote

Hi,

Code:
IsEmpty(Sheet.getCellbyPosition(x,y))
should alwas yield false, as IsEmpty only yields true for non-set variables. The cell, however, always exists, even if it is empty.
For checking empty cells, try
Code:
If(Sheet.getCellbyPosition(x,y).String<>"") then
or, just to exclude the case that you have a formula in the cell, returning ""
Code:
If((Sheet.getCellbyPosition(x,y).String<>"")  and (Sheet.getCellbyPosition(x,y).Formula<>"") )then


BTW, questions like this are better in the macro forum

Good luck,

ms777
Back to top
View user's profile Send private message
SomeoneHere
OOo Enthusiast
OOo Enthusiast


Joined: 28 Oct 2005
Posts: 116
Location: Boston, Massachusetts USA

PostPosted: Mon Nov 21, 2005 11:34 am    Post subject: Reply with quote

thanks for the help I will have to give it a try
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Nov 21, 2005 1:10 pm    Post subject: Reply with quote

Hi, I would use the builtin query-methods:
Code:
if (oCell.getType <> com.sun.star.table.CellContentType.FORMULA)AND (oCell.getString="") then do_something

I believe the following technique is more effective on large tables:
Code:

oQry = myRange.queryContentCells(com.sun.star.sheet.CellFlags.FORMULA).createEnumeration
while oQry.hasMoreElements
  oSubRange = oQry.nextElement.getCells.createEnumeration
  while oSubRange.hasMoreElements
    oCell = oSubRange.nextElement
    if oCell.getString = "" then do_something
  wend
wend

But why not using the builtin conditional formats?
AND(TYPE(A1 =8 );A1="") returns true if there is "" as formula-result.
this can be used as a condition for conditional formatting.
Set the cell-Style to any kind of "red alert" if the condition is true. This is not too restrictive, since it is not a database.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group