Difficult Macro: Selecting a cell that is always changing

[theclaah]

If I have a spreadsheet which is never the same size, and I need to select a cell from a given column which is never the same value. What would the macro command be?

I am guessing it would be something like a MIN command within a selected range to find a value less than zero?

Thanks.

PS. In case you need more information, the project I am trying to complete is a macro that will take data that is captured from a a test I am running and through a macro give me the data I require. I am just starting so any help is greatly appreciated.

The data consist of 7 columns labeled as TIME_SEC, VOLTAGE, CURRENT, IN_PRESS, OUT_PRESS, IN_TEMP and UNIT_TEMP.

The number of rows can vary but are usually from 10000 to 16000.

Here is the information I need to extract from this data.

1. Determine the opening time. Opening time is defined as the time from when power is applied to a sharp dip in current is noted.

2. Determine the closing time. Closing time is defined as the time from removing power until the first point outlet pressure is continuously decaying at a rate equal to or greater than -2 psig/msec.

3. Determine Vent time. Vent time is defined as the time from removing power until outlet pressure decays to 150 ±5 psig.

 

[theclaah]

TsTom Check your email.

Thanks for the help.

 

[theclaah]

Here is what I have so far. Now I am stuck at insert the formula. How do I insert a formula if I do not know what row number will be? It varies.

Sub Response()
Dim c
For Each c In Range("B2:B2000")
If c.Value < 1 Then
c.Offset(0, -1).Copy
Range("I1").PasteSpecial
c.Offset(50, 1).Select
End If
Next
Range(Selection, Selection.Offset(100, 0)).Select
AMP_Rng = Range(Selection, Selection)
Low_AMP = Application.WorksheetFunction.Min(AMP_Rng)
With Worksheets(1).Range(Selection, Selection)
Set c = .Find(Low_AMP, LookIn:=xlValues)
c.Offset(0, -2).Copy
Range("J1").PasteSpecial
Range("K1").Value = "=J1-I1"
Opn_Time = Range("K1")
MsgBox Opn_Time
End With

Range("B1000").Activate
Range(Selection, Selection.End(xlDown)).Select
For Each c In Range(Selection, Selection)
If c.Value < 1 Then
c.Offset(0, -1).Copy
Range("L1").PasteSpecial
End If
Next

End Sub

 

[Legacy 98055]

<pre>
Hi Terry,
I wanted to make sure I was coding this right?
Question about low point of dip...

1.
Start in cell B2
Read down until we find a value <1

Found in Row 385

2.
Start in Column C at Row 385
Found dip at C449

Is this correct?
Thanks,
Tom
</pre>

 

[theclaah]

Yes that is the dip and you got the lowest point. I am working this right now also. Now I am having trouble with the finding the first value that is below -2.
Here is what I have so far.

Sub Response()
Dim c
For Each c In Range("B2:B2000")
If c.Value < 1 Then
c.Offset(0, -1).Copy
Range("I1").PasteSpecial
c.Offset(50, 1).Select
End If
Next
Range(Selection, Selection.Offset(100, 0)).Select
AMP_Rng = Range(Selection, Selection)
Low_AMP = Application.WorksheetFunction.Min(AMP_Rng)
With Worksheets(1).Range(Selection, Selection)
Set c = .Find(Low_AMP, LookIn:=xlValues)
c.Offset(0, -2).Copy
Range("J1").PasteSpecial
Range("K1").Value = "=J1-I1"
Opn_Time = Range("K1")
MsgBox Opn_Time
End With

Range("B1000").Activate
Range(Selection, Selection.End(xlDown)).Select
For Each c In Range(Selection, Selection)
If c.Value < 1 Then
c.Offset(0, -1).Copy
Range("L1").PasteSpecial
Range("H2").Value = "=E3-E2"
Range("H2").Select
Range("H2").AutoFill Destination:=Range(Selection, Selection.End(xlDown))
c.Offset(0, 6).Select
End If
Next
'Range(Selection, Selection.End(xlDown)).ClearContents

End Sub

 

[Legacy 98055]

<pre>
Power On
0.383 $A$385

Power Off
0.384 $A$386

Opening Time
-0.064 $A$385 - $A$449

Closing Time
-4.139 $A$386 - $A$4525

Venting Time
-0.089 $A$386 - $A$475</pre>

These are the answers I got.
Are they correct?
Thanks,
Tom
This message was edited by TsTom on 2002-04-27 21:13

 

[theclaah]

Well the answers I got the manual way are

Opening Time = 0.063
Closing Time = 0.039
Vent Time = 0.134

I think I got it figured out. Not saure that it is the cleanest way but it does seem to work. Do you want to take a look at it? I can send it to ya.

 

[theclaah]

The .064 is correct since it uses the first value below 1.

The calculation for closing would be
$A$4526 - $A$4486

For Vent
$A$4621 - $A$4486

 

[theclaah]

I am sorry that you feel that way. But I beleive you subtratcted the Power_On, not the Power_OFF like you stated in your summary.

Thank you for all your help. I couldn't have done it without you!