 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008How 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  SignatureJeff Ciaccio Physics and AP Physics Teacher Sprayberry High School; Marietta, GA Blog: http://sprayberry.typepad.com/ciaccio Sub findandoffset() Range("c2") = Columns(1).Find(2, lookat:=xlPart).Offset(-1, 1) End Sub SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com 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 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 SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com Sub findandoffset() Range("c2") = Columns(1).Find(2, lookat:=xlPart).Offset(-1, 1) End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Jeff Ciaccio" <noname@noreply.org> wrote in message news:OS%236fmDXIHA.6044@TK2MSFTNGP05.phx.gbl... 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 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 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 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!! 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" <noname@noreply.org> wrote in message news:OS%236fmDXIHA.6044@TK2MSFTNGP05.phx.gbl... 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 Maybe you don't want code, just a formula in a cell. You limit the two intervals to 100 seconds total at .1 second plot intervals. That means interval 1 could use a maximum of 1000 rows or C7:C1007. Time must increase as it goes down the column, so whatever row interval 1 ends on, it must be the maximum number in that range. The opposite would be true for the last position in column D. It would be the minimum position number. So for interval 2 to begin time in E7, put the formula "=MAX(C7:C1007) + .1" without the quotes. In F7 you would apply your acceleration formula to this "=MIN(D7:D1007)". Does this help any? Mike F 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" <mikefogleman@insightbb.com> wrote in message news:dJCdnaeH6N1jIQnanZ2dnUVZ_rqlnZ2d@comcast.net... 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" <noname@noreply.org> wrote in message news:OS%236fmDXIHA.6044@TK2MSFTNGP05.phx.gbl... 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.