[ut_libet]

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.

[JE McGimpsey]

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.

[Gary''s Student]

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.

[ut_libet[_2_]]

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.