That would do it... If you need it to be dynamic then use something like
this...

Public Function FindStuff(ByVal rngToSearch As Range) As String
Dim rng As Range

FindStuff = "No Value Found"
For Each rng In rngToSearch
If rng.Value < 0 Then
FindStuff = Cells(1, rng.Column).Value
Exit For
End If
Next rng
End Function
--
HTH...

Jim Thomlinson


"Dave F" wrote:

Interesting.

Let's say I also want to do a similar search for range BA3:BL3 but still
return the value in range BA1:BL1. Would I change the rng.Offset(-1,
0).Value to rng.Offset(-2, 0).Value ?

Dave

--
Brevity is the soul of wit.


"Jim Thomlinson" wrote:

Give this a whirl. You can use it in code or as a UDF...

Sub whatever()
MsgBox FindStuff(Sheets("sheet1").Range("BA2:BL2"))
End Sub

Public Function FindStuff(ByVal rngToSearch As Range) As String
Dim rng As Range

FindStuff = "No Value Found"
For Each rng In rngToSearch
If rng.Value < 0 Then
FindStuff = rng.Offset(-1, 0).Value
Exit For
End If
Next rng
End Function
--
HTH...

Jim Thomlinson


"Dave F" wrote:

I need a formula for the following:

Given range BA2:BL2, I need to find the first value in this range 0,
reading left to right, and then return the text in the corresponding cell in
the range BA1:BL1.

Example, if the first value 0 in the range BA2:BL2 is in BD2, I need the
text in BD1 returned.

Clear?

I hope so.

Thanks.

Dave
--
Brevity is the soul of wit.