MATCH an unknown number
 

I need to find the location of the first cell in a row containing a number.
(Cells that are not numbers are #N/A, #VALUE!, or #REF! errors).

For instance, I need to look in B55:IV55 to find the location of the first
number, left-to-right. If the first number is in B55 I want a 1, or if it's
in C55 I want a 2, or if it's in D55 I want a 3, etc.. How can I use MATCH
(or any other function) to return the relative cell location if it there can
be any number in the cell?

Thanks in advance.

MATCH an unknown number
 

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MATCH(TRUE,ISNUMBER(B55:IV55),FALSE)

However, I'd also recommend trapping those errors - in general leaving
"expected" errors is a bad idea - it leads to missing real logic,
business or data entry errors.



In article ,
ut_libet wrote:

I need to find the location of the first cell in a row containing a number.
(Cells that are not numbers are #N/A, #VALUE!, or #REF! errors).

For instance, I need to look in B55:IV55 to find the location of the first
number, left-to-right. If the first number is in B55 I want a 1, or if it's
in C55 I want a 2, or if it's in D55 I want a 3, etc.. How can I use MATCH
(or any other function) to return the relative cell location if it there can
be any number in the cell?

Thanks in advance.

MATCH an unknown number
 

Try this small User Defined Function:

Function firstnumber(r As Range) As Integer
firstnumber = 1
For Each rr In r
If Not IsEmpty(rr) Then
If IsNumeric(rr.Value) Then Exit Function
End If
firstnumber = firstnumber + 1
Next
firstnumber = 0
End Function

Use it in the worksheet like:
=firstnumber(B55:IV55)

It will ignore blanks, errors, and Text
--
Gary''s Student - gsnu200791


"ut_libet" wrote:

I need to find the location of the first cell in a row containing a number.
(Cells that are not numbers are #N/A, #VALUE!, or #REF! errors).

For instance, I need to look in B55:IV55 to find the location of the first
number, left-to-right. If the first number is in B55 I want a 1, or if it's
in C55 I want a 2, or if it's in D55 I want a 3, etc.. How can I use MATCH
(or any other function) to return the relative cell location if it there can
be any number in the cell?

Thanks in advance.

MATCH an unknown number
 

Thank you very much! This was exactly the formula I needed.

And yes, I'll do my best to work around creating those errors.



"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MATCH(TRUE,ISNUMBER(B55:IV55),FALSE)

However, I'd also recommend trapping those errors - in general leaving
"expected" errors is a bad idea - it leads to missing real logic,
business or data entry errors.



In article ,
ut_libet wrote:

I need to find the location of the first cell in a row containing a number.
(Cells that are not numbers are #N/A, #VALUE!, or #REF! errors).

For instance, I need to look in B55:IV55 to find the location of the first
number, left-to-right. If the first number is in B55 I want a 1, or if it's
in C55 I want a 2, or if it's in D55 I want a 3, etc.. How can I use MATCH
(or any other function) to return the relative cell location if it there can
be any number in the cell?

Thanks in advance.