stock returns/average/formatting

[ctallen23]

Hi everyone. thank you for looking over my problems. I am trying to have
macros calculate monthly stock returns and average the stock prices as well.
I can't get a macro that will calculate the return based off of the p0, p1
stock equation. p0 = prior price, p1 = current price. My equation
calculates the returns but once the equation gets to a cell that has nothing
yet in it, I either get # value or it will return what cell b25 has in it.
Also, the sub procedure is set up to accomodate many tasks simultaneously.
The procedure adds a % to cells that do not have anything in them yet. How
can I get an equation that will make cells blank, not zero. Is it possible
to set the procedures to a range since these stocks, dates and returns will
grow pending person's objective.

I am trying to have a range that can loop to determine stock returns, which
cells get the percent sign, and leaving those cells blank should the prior
stock price, p0, not equal a value.


Function stockreturns(p1 As Variant, p0 As Variant) As Double
Dim cellval As Double

stockreturns = Log(p1 / p0)

End Function

Sub FillRange2()

Dim row As Integer
Dim col As Integer
Dim stockmax As Integer

stockmax = Range("b2")

For row = 0 To 11
For col = 0 To stockmax - 1

If Range("b25") <> vbEmpty Then
Range("b25").Copy ActiveCell.Offset(row, col)

End If

Sheets("Sheet1").Range("b25").Offset(row, col).NumberFormat = "0.000%"

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With

Next col
Next row

End Sub

much appreciated!

first time poster, long time reader.

chris

 

[Guest]

You can use .clearcontents to remove data from a cell.

example cells.clearcontenets.

checkingg for empty cells use isempty(cells(1,"A")) or the inverse
not isempty(cells(1,"A"))