| View previous topic :: View next topic |
| Author |
Message |
SomeoneHere
OOo Enthusiast
Joined: 28 Oct 2005
Posts: 116
Location: Boston, Massachusetts USA
|
 Posted: Mon Nov 21, 2005 10:05 am Post subject: Is there a way to tell if a cell has nothing in it? |
 |
|
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 |
|
 |
ms777
Super User
Joined: 07 Feb 2004
Posts: 1355
|
| Posted: Mon Nov 21, 2005 10:54 am Post subject: |
|
|
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 |
|
|
SomeoneHere
OOo Enthusiast
Joined: 28 Oct 2005
Posts: 116
Location: Boston, Massachusetts USA
|
| Posted: Mon Nov 21, 2005 11:34 am Post subject: |
|
|
| thanks for the help I will have to give it a try |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Nov 21, 2005 1:10 pm Post subject: |
|
|
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 |
|
|
|
