Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=sign(--counta(range)) not working righ t
I have been using the above function for the purpose of
telling me if there is any data in range (row of cells) For some reason the function is still producing a 1 even with no vavlues numbers or chacters in the range. I have tried several cell formats and for some resaon it is not work, well sometime yes and sometime no any suggestions |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=sign(--counta(range)) not working righ t
A formula such as =IF(A110,"","OK") will display nothing when A1 is greater
than 10 If this is in the range A2:D2 then =COUNTA(A2:D2) will return 1, even if all the cells in the range look blank best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email " wrote in message ... I have been using the above function for the purpose of telling me if there is any data in range (row of cells) For some reason the function is still producing a 1 even with no vavlues numbers or chacters in the range. I have tried several cell formats and for some resaon it is not work, well sometime yes and sometime no any suggestions |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=sign(--counta(range)) not working righ t
Maybe you have something in that range that you can't see--either space
characters, HTML non-breaking space characters, formulas that evaluate to "" or values that were converted from formulas that evaluated to "". What does: =Counta(range) return? What is that range's address? wrote: I have been using the above function for the purpose of telling me if there is any data in range (row of cells) For some reason the function is still producing a 1 even with no vavlues numbers or chacters in the range. I have tried several cell formats and for some resaon it is not work, well sometime yes and sometime no any suggestions -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=sign(--counta(range)) not working righ t
Exactly, I know there has to be something in the range.
I had to consolidate a few spreedsheet, bunch of copy and pasting. I did the simple Counta, and it returned a 1 so there has to be somthing in range unseen. The mystery remains any suggestions "Dave Peterson" wrote: Maybe you have something in that range that you can't see--either space characters, HTML non-breaking space characters, formulas that evaluate to "" or values that were converted from formulas that evaluated to "". What does: =Counta(range) return? What is that range's address? wrote: I have been using the above function for the purpose of telling me if there is any data in range (row of cells) For some reason the function is still producing a 1 even with no vavlues numbers or chacters in the range. I have tried several cell formats and for some resaon it is not work, well sometime yes and sometime no any suggestions -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=sign(--counta(range)) not working righ t
" wrote:
so there has to be somthing in range unseen. The mystery remains any suggestions Perhaps the following macro will help. Select the range and execute the following macro: Sub findit() Dim cell As Range For Each cell In Selection If Not IsEmpty(cell) Then MsgBox cell.Address Next cell End Sub For each non-empty cell identified by the macro (your are expecting only one), select the cell and go from there. First, look at its contents shown in the Formula Bar. If it still appears empty, execute =CODE(A1) to see the character that appears blank. Or simply press Delete to delete the cell contents. Note: To create the macro, press alt+F11 to open a VB window. In the VB window, click Insert, then Module to open a VB Editor pane. Copy-and-paste the macro text above into the VB Editor pane. Then return to the Excel window, select the range, and press alt+F8 to select and run the macro. ----- original message ----- " wrote in message ... Exactly, I know there has to be something in the range. I had to consolidate a few spreedsheet, bunch of copy and pasting. I did the simple Counta, and it returned a 1 so there has to be somthing in range unseen. The mystery remains any suggestions "Dave Peterson" wrote: Maybe you have something in that range that you can't see--either space characters, HTML non-breaking space characters, formulas that evaluate to "" or values that were converted from formulas that evaluated to "". What does: =Counta(range) return? What is that range's address? wrote: I have been using the above function for the purpose of telling me if there is any data in range (row of cells) For some reason the function is still producing a 1 even with no vavlues numbers or chacters in the range. I have tried several cell formats and for some resaon it is not work, well sometime yes and sometime no any suggestions -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counta in array formula not working | Excel Worksheet Functions | |||
=NOT(ISNUMBER(RIGH(V670,5))) | Excel Discussion (Misc queries) | |||
COUNTA Formula not working | New Users to Excel | |||
How can i put a negative sign on a range of selected cells? | Excel Discussion (Misc queries) | |||
COUNTA Function not working =COUNTA(C3:C69,"NH") | Excel Worksheet Functions |