|
How to capture the cell above
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 |
How to capture the cell above
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 |
How to capture the cell above
It seems that Interval1 ends from a user input time and is plotted in ..1s intervals. So it takes 2s/.1s for Interval 1 to elapse. That is 20. So interval 1 will end 20 cells below the start time cell. If the start time cell is D14, then the end cell is D34.
Sub Interval_1() Dim MyVal As Variant Dim intrvl1 As Long, CellCnt As Long intrvl1 = Range("C4").Value 'user input cell CellCnt = intrvl1 / 0.1 MyVal = Cells(14 + CellCnt, 4).Value '14 is start row End Sub Mike F "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 |
How to capture the cell above
Thanks Mike, but please forgive me for not being VBA savay. How do I get a different cell to run this sub automatically? For example, I think I'd like to put this to the right of the Starting Poisition (cell C7), so maybe E7 would be "Starting Position for second interval" and F7 would contain the return value from the subroutine.
Thanks!! "Mike Fogleman" wrote in message . .. It seems that Interval1 ends from a user input time and is plotted in ..1s intervals. So it takes 2s/.1s for Interval 1 to elapse. That is 20. So interval 1 will end 20 cells below the start time cell. If the start time cell is D14, then the end cell is D34. Sub Interval_1() Dim MyVal As Variant Dim intrvl1 As Long, CellCnt As Long intrvl1 = Range("C4").Value 'user input cell CellCnt = intrvl1 / 0.1 MyVal = Cells(14 + CellCnt, 4).Value '14 is start row End Sub Mike F "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 |
| All times are GMT +1. The time now is 03:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com