Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Difficult Macro: Selecting a cell that is always changing

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •