Say I have a column starting at F5, it can be any length. The values in the
cells are numeric, some positive some negative. I want to look down the cells
and when I see the first change from a negative to a positive, I perform some
calculation using the values in that row. I then set 2 variables, one called
open, another called closed. Set open to true, closed to false. Keep looking
down the column and when the values switches from positive to negative and
open equals true and closed equal false, do some calculations. Keep doing
that until the end of the column. I am lost and need some help to get started
please? Thanks in advance.

Hi Anita

Try the below and feedback..

Sub MyMacro()
Dim lngRow As Long, varTemp As Variant, varChange As Variant
For lngRow = 5 To Cells(Rows.Count, "F").End(xlUp).Row
varTemp = Range("F" & lngRow) 0
If varChange < vbNull And varChange < varTemp Then
varChange = varTemp
'-ve to +ve varChange=True, +ve to -ve varChange=False

'Do your calculations here

End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Anita" wrote:

Say I have a column starting at F5, it can be any length. The values in the
cells are numeric, some positive some negative. I want to look down the cells
and when I see the first change from a negative to a positive, I perform some
calculation using the values in that row. I then set 2 variables, one called
open, another called closed. Set open to true, closed to false. Keep looking
down the column and when the values switches from positive to negative and
open equals true and closed equal false, do some calculations. Keep doing
that until the end of the column. I am lost and need some help to get started
please? Thanks in advance.

Thanks so much for that Jacob. Works exactly as it should and fast response!

"Jacob Skaria" wrote:

Hi Anita

Try the below and feedback..

Sub MyMacro()
Dim lngRow As Long, varTemp As Variant, varChange As Variant
For lngRow = 5 To Cells(Rows.Count, "F").End(xlUp).Row
varTemp = Range("F" & lngRow) 0
If varChange < vbNull And varChange < varTemp Then
varChange = varTemp
'-ve to +ve varChange=True, +ve to -ve varChange=False

'Do your calculations here

End If
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Anita" wrote:

Say I have a column starting at F5, it can be any length. The values in the
cells are numeric, some positive some negative. I want to look down the cells
and when I see the first change from a negative to a positive, I perform some
calculation using the values in that row. I then set 2 variables, one called
open, another called closed. Set open to true, closed to false. Keep looking
down the column and when the values switches from positive to negative and
open equals true and closed equal false, do some calculations. Keep doing
that until the end of the column. I am lost and need some help to get started
please? Thanks in advance.