Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Hi
I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
On Sat, 9 Jun 2007 06:14:20 +1000, "Mark" wrote:
Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark You only need to post this question once. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
This can be done with a formula, but, what kind of result do you want? Do
you want the value itself? Do you want the cell address? Do you want the relative position of the value? Is the data numeric? Are there any negative numbers? Are there any formulas in the range that return formula blanks? Lots of things to consider for the best solution! Biff "Mark" wrote in message ... Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Try this:
Assume your data in row 1 =INDEX(1:1,MATCH(TRUE,1:1<0,0)) ctrl+shift+enter, not just enter "Mark" wrote: Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
On Fri, 8 Jun 2007 15:31:00 -0700, Teethless mama
wrote: Try this: Assume your data in row 1 =INDEX(1:1,MATCH(TRUE,1:1<0,0)) ctrl+shift+enter, not just enter Of course, that will match on a text entry also. I guess that is non-zero, as the OP requested. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
I know Ron, sorry but I had a wireless net failure. It happens.
"Ron Rosenfeld" wrote in message ... On Sat, 9 Jun 2007 06:14:20 +1000, "Mark" wrote: Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark You only need to post this question once. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Yes good question. The ideal for my situation would be the value of the
first instance. Many thanks. "T. Valko" wrote in message ... This can be done with a formula, but, what kind of result do you want? Do you want the value itself? Do you want the cell address? Do you want the relative position of the value? Is the data numeric? Are there any negative numbers? Are there any formulas in the range that return formula blanks? Lots of things to consider for the best solution! Biff "Mark" wrote in message ... Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
On Mon, 11 Jun 2007 10:22:41 +1000, "Mark"
wrote: I know Ron, sorry but I had a wireless net failure. It happens. Answered in your other thread: --------------------------------- What do you mean by "find" the first cell? This **array-entered** formula will return the column number of the first cell in row 1 with a non-zero entry (enter with <ctrl-shift-enter) =MATCH(1,ISNUMBER(1:1)*(1:1<0),0) This **array-entered** formula will return the value in that cell: =INDIRECT(ADDRESS(1,MATCH(1,ISNUMBER(1:1)*(1:1<0) ,0))) ------------------------------- --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Try this:
Assuming the target row is row 1. =INDEX(1:1,MATCH(1,INDEX(--(1:1<0),,),0)) This will return the data from the first cell (left to right) that is not a numeric 0. Biff "Mark" wrote in message ... Yes good question. The ideal for my situation would be the value of the first instance. Many thanks. "T. Valko" wrote in message ... This can be done with a formula, but, what kind of result do you want? Do you want the value itself? Do you want the cell address? Do you want the relative position of the value? Is the data numeric? Are there any negative numbers? Are there any formulas in the range that return formula blanks? Lots of things to consider for the best solution! Biff "Mark" wrote in message ... Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Many thanks. However, this is giving me a circular reference error when I
place it the first column of the row. Any solution to this? Mark "T. Valko" wrote in message ... Try this: Assuming the target row is row 1. =INDEX(1:1,MATCH(1,INDEX(--(1:1<0),,),0)) This will return the data from the first cell (left to right) that is not a numeric 0. Biff "Mark" wrote in message ... Yes good question. The ideal for my situation would be the value of the first instance. Many thanks. "T. Valko" wrote in message ... This can be done with a formula, but, what kind of result do you want? Do you want the value itself? Do you want the cell address? Do you want the relative position of the value? Is the data numeric? Are there any negative numbers? Are there any formulas in the range that return formula blanks? Lots of things to consider for the best solution! Biff "Mark" wrote in message ... Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Many thanks. However, this is also giving me a circular reference error when
I place it the first column of the row. Mark "Teethless mama" wrote in message ... Try this: Assume your data in row 1 =INDEX(1:1,MATCH(TRUE,1:1<0,0)) ctrl+shift+enter, not just enter "Mark" wrote: Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Oh, its ok I've fixed it. I've simply specified the cells (b1:iq1) for the
array. Many thanks. Mark "Mark" wrote in message ... Many thanks. However, this is giving me a circular reference error when I place it the first column of the row. Any solution to this? Mark "T. Valko" wrote in message ... Try this: Assuming the target row is row 1. =INDEX(1:1,MATCH(1,INDEX(--(1:1<0),,),0)) This will return the data from the first cell (left to right) that is not a numeric 0. Biff "Mark" wrote in message ... Yes good question. The ideal for my situation would be the value of the first instance. Many thanks. "T. Valko" wrote in message ... This can be done with a formula, but, what kind of result do you want? Do you want the value itself? Do you want the cell address? Do you want the relative position of the value? Is the data numeric? Are there any negative numbers? Are there any formulas in the range that return formula blanks? Lots of things to consider for the best solution! Biff "Mark" wrote in message ... Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero
Oh, its ok I've fixed it. I've simply specified the cells (b1:iq1) for the
array. Many thanks. Mark "Mark" wrote in message ... Many thanks. However, this is also giving me a circular reference error when I place it the first column of the row. Mark "Teethless mama" wrote in message ... Try this: Assume your data in row 1 =INDEX(1:1,MATCH(TRUE,1:1<0,0)) ctrl+shift+enter, not just enter "Mark" wrote: Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to find the first cell that is not zero-Thanks everyone
A hearty thanks to everyone for your kind help and support. Much
appreciated!!! These solutions all work nicely. Mark "Mark" wrote in message ... Hi I want to find the first cell that is not zero "0" across a row. I am also trying a VBA solution, but is there perchance a function that will do this? Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF function which can find a blank cell | Excel Worksheet Functions | |||
find cell function | Excel Worksheet Functions | |||
Function to find the address of a cell | Excel Worksheet Functions | |||
how can I use a function to find out if an individual cell is par. | Excel Worksheet Functions | |||
Is there a function for "not isblank" (find a cell that has a val. | Excel Worksheet Functions |