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.

[Tom Urtis]

Consider reposting your question with a few facts about your spreadsheet, and I bet you can get an answer:

(1) Is it that the quantity of rows vary but the quantity of columns (7 according to your post) will stay the same?

(2) Will the upper left cell of your data range be the same? For example, maybe you are using row 1 as your header row, with columns A:G as your 7 columns, and so the actual data will start in cell A2. Is that the case? Or, when you say the rows are always changing, do you mean that sometimes the first row is row 5, then tomorrow maybe it's row 112, etc, and all over the map like that?

(3) The 3 points at the end of your post seem like results of calculations that will either be part of, or be extracted from and be added to, the data in the 7 columns. Please specify.

(4) Whatever your answer is to that #3 question above, please tell us what kind of number we should help you locate. For instance, is it the lowest number in that column? Th highest number in that entire dataset? Is it a number with a certain percentage change, or what? And, what if two or more values exist that meet your "search & find" criteria...what then?

So, please describe the range, what we should look for, and where.

[theclaah]

(1) Yes the rows vary but the colums will remain at 7.

(2) Yes the first row will always have the header info as mention above, and columns A:G. Actual data starts at A2.

(3) Yes the points I need at the end are results of calculations. My intent is to have the macro open the spreadsheet, do the calculations, close that worksheet and the post the results in a seperate worksheet.

Now what I am really struggling with is what command do I use in a macro to find the min value in a selected range of cells.

This is how the information/calculations are currently done for each requirement.

Requirement
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.

Manual Calculation.
Scroll down paying attention to column B (VOLTAGE) and stop when the data is less than 1.0 (it varies at this stage between 16.0-17.999). The point at which the data is <1 copy the data on that row but from column "A" (TIME_SEC). This tells you a time reference of when power was toggled and I will refer to this as POWER_ON reference. Next, scroll down from this point, paying attention to column "C". The point at which the data dips is what we are looking for. In other words, the data is rising gradually as we scroll down then it takes a dip. The lowest point in the dip is what we are looing for. We take the data from column "A" (TIME_SEC) in this row and subtract it from the POWER_ON referenced above. The result is the opening time.

Requirement
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.

Manual Calculation.
Go to last cell in column "A". Then scroll up paying attention to column B (VOLTAGE) and stop when the data is less than 1.0 (it varies at this stage between 27.0-29.999). The point at which the data is <1 copy the data on that row but from column "A" (TIME_SEC). This tells you a time reference of when power was toggled and I will refer to this as POWER_OFF reference. Go to column "H" and input a formula to subtract data in this row but column "E" (OUT_PRESS) from the data in column "H" just above this row. Fill this formula down to end of worksheet. Scroll down paying attention to column "H" and you are looking for when the data stays below -2.0 (Notice negative number). This point we need to subtract the data from column "A" (TIME_SEC) from the POWER_OFF reference above. The result is closing time.

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

Manual Calculation.
From the POWER_OFF referenced in requirement 2, scroll down from this point, paying attention to column "E" (OUT_PRESS). The point at which the data is at 150 ±5 is what we are looking for. We take the data from column "A" (TIME_SEC) in this row and subtract it from the POWER_OFF referenced above. The result is the vent time.

[Tom Schreiner]

I now have a post-readpost headache.
Will glady wait and let someone else tackle this one.
Tom

[theclaah]

What I really need help is activated the cell in a range that is less than 1. Can seem to get it right in my macro. I have tried.

Range("B1:B2000").Find(<1, LookIn:=xlValues).Activate

Application.WorksheetFunction.Min(Selection).Activate

[Tom Schreiner]

Start in cell B2

Read down until we find a value <1

At this row grab the data from Column A

Refrnc:= (Power On)

At this same row, in Column C, start

reading down until we find the lowest 

point in a dip of value

At this row grab value from Column A

Sutract this new value from Power On

Result = (Opening Time)?



Start in last value found in Column A

In the same row in Column B,

go up until a value of <1 is found.

Grab value at this row in Column A

(Power Off)

For ex.  Row 12000

Go to Column H of this row and

place formula:

H11999 - E12000

Fill down column H to the

last cell containing data.

Use Column A as reference for

last Cell?

At row 12000, scroll down values

until < -2

Grab value from Column A in same row



Power Off - Column A Value = Closing Time?



Beginning at E12000

scroll down values until we find 

>144 <156 ?

Grab value from this row in Column A



Power Off - Value in Column A = Venting Time ?









Your Quote.  I am not grasping this:

"Go to column "H" and input a formula 

to subtract data in this row but column "E" 

(OUT_PRESS) from the data in column "H" just 

above this row."



Also, 150 ±5 

Does that = >144 <156



Sorry if I just repeated you in other words.

I needed to repeat you in my own words

to see if I had a chance of grasping what you

are after...



Thanks,

Tom

[Tom Schreiner]

Hi
This will find the first value of <1
in range ("B2:B1000")

Just call form your routine..

Sub FindLess_1_In_B()

Dim c

For Each c In Range("B2:B2000")

    If c.Value < 1 Then

        c.Activate

        Exit Sub

    End If

Next

End Sub

Tom

[theclaah]

Yes, 150 ±5 equals >144 <156.

I know I was not sure I had it right as I was typing it. Looks like you got it right except where you say

Quote:
For ex. Row 12000
Go to Column H of this row and
place formula:
H11999 - E12000

This should be
Go to Column H of this row and
place formula:
E12000 - E11999

[Tom Schreiner]

To find the minimum value in the same range use:

Sub FindMinimumValue_In_B()

    Dim answer

    Dim myRange As Range

    Set myRange = Range("B2:B2000")

    answer = Application.WorksheetFunction.Min(myRange)

End Sub

Tom

P.S.

This really does you no good because it does not activate or even return an address...


[ This Message was edited by: TsTom on 2002-04-27 18:23 ]

[Tom Schreiner]

I'd like to play with this and do away
with the manual calculation...
If you'd like, send me the book and I'll throw something together...
TsTom@HotMail.Com

Please make sure I am understanding how to do this by re-reading my summary of what I think you said...

I saw your correction pertaining to the formula.

Thanks,
Tom