How to capture the cell above

J

Jeff Ciaccio

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
 
D

Don Guillett

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 
D

Don Guillett

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
(e-mail address removed)
Sub findandoffset()
Range("c2") = Columns(1).Find(2, lookat:=xlPart).Offset(-1, 1)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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
 
M

Mike Fogleman

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
 
J

Jeff 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

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
 
M

Mike Fogleman

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!!
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a cell value to trigger macro to run 1
Can I get Excel to call my macro for number formatting? 5
WTC Towers: The Case For Controlled Demolition 2
Values for charts 4
value is changing at the end of Call Sub 2
How to change the row number in macro code? 1
How do I set my X axis to include equal increments of a day? 2
How to calculate (generate) a cell reference 6

Top