Thanks:  0
Likes:  0

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

Thanks for the help.

2. 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. ```
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. 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. ```
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. 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. 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. 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!

## User Tag List

#### Posting Permissions

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