Hi,
I have got the following procedure that checks the whether the cells in
LSRatio are filled before making the calculation. I would like to change this
so that I check that ShortPrice AND LongPrice are different from zero and of
the same sign before making the calculation. How do I do this?
i = 0
For Each cell In Range("LSRatio")
i = i + 1
If Not IsEmpty(cell) Then
Range("ShortCumReturn")(i).Value = (Range("ShortPrice")(i).Value /
Range("LongPrice").Cells(1).Value) - 1
Else
Exit For
End If
Next
Thanks!

i = 0
For Each cell In Range("LSRatio")
i = i + 1
set s = Range("shortprice")(i)
set l = Range("longprice")(i)
if s< 0 and l < 0 then
if sgn(s) = sgn(i) then
Range("ShortCumReturn")(i).Value = s/l - 1
end if
end if
Next


Assumes you don't want to clear the shortCumReturn cell if the conditions
are not met.
--
Regards,
Tom Ogilvy

"Benoit" wrote:

Hi,
I have got the following procedure that checks the whether the cells in
LSRatio are filled before making the calculation. I would like to change this
so that I check that ShortPrice AND LongPrice are different from zero and of
the same sign before making the calculation. How do I do this?
i = 0
For Each cell In Range("LSRatio")
i = i + 1
If Not IsEmpty(cell) Then
Range("ShortCumReturn")(i).Value = (Range("ShortPrice")(i).Value /
Range("LongPrice").Cells(1).Value) - 1
Else
Exit For
End If
Next
Thanks!

Change ranges to suit:

Sub a()
If Application.And(Range("a1") < 0, Range("b1") < 0) Or
Application.And(Range("a1") 0, Range("b1") 0) Then
MsgBox "OK"
End If
End Sub

HTH

"Benoit" wrote:

Hi,
I have got the following procedure that checks the whether the cells in
LSRatio are filled before making the calculation. I would like to change this
so that I check that ShortPrice AND LongPrice are different from zero and of
the same sign before making the calculation. How do I do this?
i = 0
For Each cell In Range("LSRatio")
i = i + 1
If Not IsEmpty(cell) Then
Range("ShortCumReturn")(i).Value = (Range("ShortPrice")(i).Value /
Range("LongPrice").Cells(1).Value) - 1
Else
Exit For
End If
Next
Thanks!

Tom,
SGN should be SIGN?

"Tom Ogilvy" wrote:

i = 0
For Each cell In Range("LSRatio")
i = i + 1
set s = Range("shortprice")(i)
set l = Range("longprice")(i)
if s< 0 and l < 0 then
if sgn(s) = sgn(i) then
Range("ShortCumReturn")(i).Value = s/l - 1
end if
end if
Next


Assumes you don't want to clear the shortCumReturn cell if the conditions
are not met.
--
Regards,
Tom Ogilvy

"Benoit" wrote:

Hi,
I have got the following procedure that checks the whether the cells in
LSRatio are filled before making the calculation. I would like to change this
so that I check that ShortPrice AND LongPrice are different from zero and of
the same sign before making the calculation. How do I do this?
i = 0
For Each cell In Range("LSRatio")
i = i + 1
If Not IsEmpty(cell) Then
Range("ShortCumReturn")(i).Value = (Range("ShortPrice")(i).Value /
Range("LongPrice").Cells(1).Value) - 1
Else
Exit For
End If
Next
Thanks!

No. SGN is the VBA fuction to return the sign of the number

But I do see a typo - correction below

i = 0
For Each cell In Range("LSRatio")
i = i + 1
set s = Range("shortprice")(i)
set l = Range("longprice")(i)
if s< 0 and l < 0 then
' change the next line to use "L" instead of i
if sgn(s) = sgn(l) then
Range("ShortCumReturn")(i).Value = s/l - 1
end if
end if
Next

--
Regards,
Tom Ogilvy


"Toppers" wrote in message
...
Tom,
SGN should be SIGN?

"Tom Ogilvy" wrote:

i = 0
For Each cell In Range("LSRatio")
i = i + 1
set s = Range("shortprice")(i)
set l = Range("longprice")(i)
if s< 0 and l < 0 then
if sgn(s) = sgn(i) then
Range("ShortCumReturn")(i).Value = s/l - 1
end if
end if
Next


Assumes you don't want to clear the shortCumReturn cell if the
conditions
are not met.
--
Regards,
Tom Ogilvy

"Benoit" wrote:

Hi,
I have got the following procedure that checks the whether the cells
in
LSRatio are filled before making the calculation. I would like to
change this
so that I check that ShortPrice AND LongPrice are different from zero
and of
the same sign before making the calculation. How do I do this?
i = 0
For Each cell In Range("LSRatio")
i = i + 1
If Not IsEmpty(cell) Then
Range("ShortCumReturn")(i).Value = (Range("ShortPrice")(i).Value /
Range("LongPrice").Cells(1).Value) - 1
Else
Exit For
End If
Next
Thanks!