How do I select the first occurance of a number in a column

[Corner920]

How do I select the first occurance of a certain number in a column and then,
as in the "Lookup" function, return a same numbered cell (different column).
I have "0's" and "1's" in my column (intermixed) and I am looking for the
first occurance of a "1". I then want to look up another value in the same
row (different column). "Lookup" function doesn't work if you have multiple
values not sorted. I'm using Excel 2003.

 

[Patrick Molloy]

=MATCH(1,C:C,FALSE)
This returns the first occurance in column C of the number 1...in my case th
eresult was 5, and row was also row 5 since my data started at row 1
If the match function is in B1, then in say B2 put

=OFFSET(G1,B1-1,0)

this uses the result of the match to get an item from column G that matches
the row indicated....the '-1' is the adjustemnt for the offset. For example
if we need row 5, then G1 offset 5 points at G6, so we reduce the offset by
one G1 offset(5-1) now points to G5

 

[KL]

Hi Corner920,

You don't have to select the searched cell in order to get the value. Try
something like this:

Sub Test()
With Columns("A:A")
MsgBox .Find( _
What:=1, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:= _
xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext) _
.Offset(0, 1).Value
End With
End Sub

or a slightly shorter version:

Sub Macro5()
With Columns("A:A")
MsgBox .Find(1, .Cells(1), xlValues, _
xlWhole, xlByRows, xlNext).Offset(0, 1).Value
End With
End Sub

Regards,
KL