After OP clarified to look in col b for 1.9, 2, 2.1

Sub findandoffset1()
For i = Cells(Rows.count, 2).End(xlUp).Row To 2 Step -1
'MsgBox Int(Cells(i, 2))
If Int(Cells(i, 2)) < Int(Cells(i - 1, 2)) Then
Range("c1") = Cells(i, 3)
Exit For
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message ...
Sub findandoffset()
Range("c2") = Columns(1).Find(2, lookat:=xlPart).Offset(-1, 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff Ciaccio" wrote in message ...
I am trying to wirte a sheet that will plot position vs time for two different time intervals, but I need to capture a value when it changes from interval 1 to interval 2. In the case below, I need to capture the 5, but it won't always be in that cell.

I'd really apprecitate any suggestions. THANKS!!


This worksheet will plot position vs. time and velocity vs. time for two time intervals up to
100 seconds.

Time 1 From 0 sec to: 2.00 sec
Time 2 From 2s to: 50.00 sec

Starting position: 50.0 m
Starting velocity: -22.50 m/s

Acceleration from 0 to 2s: 0.00 m/s^2
Acceleration from 2s to 50s: 5.00 m/s^2

time (s) position(m)
Interval 1 0 50
Interval 1 0.1 47.75
Interval 1 0.2 45.5
Interval 1 0.3 43.25
Interval 1 0.4 41
Interval 1 0.5 38.75
Interval 1 0.6 36.5
Interval 1 0.7 34.25
Interval 1 0.8 32
Interval 1 0.9 29.75
Interval 1 1 27.5
Interval 1 1.1 25.25
Interval 1 1.2 23
Interval 1 1.3 20.75
Interval 1 1.4 18.5
Interval 1 1.5 16.25
Interval 1 1.6 14
Interval 1 1.7 11.75
Interval 1 1.8 9.5
Interval 1 1.9 7.25
Interval 1 2 5
Interval 2 2.1
Interval 2 2.2


--
Jeff Ciaccio
Physics and AP Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio