Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

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

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

    Default

    TsTom Check your email.

    Thanks for the help.

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

    Default

    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



  3. #13
    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 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

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

    Default

    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

  5. #15
    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


    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




    These are the answers I got.
    Are they correct?
    Thanks,
    Tom

    [ This Message was edited by: TsTom on 2002-04-27 21:13 ]

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

    Default

    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.

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

    Default

    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


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

    Default

    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!

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
  •