Stopped working, not sure why

[ajc5382]

Below is code for my volatility calculation.

rngvol is the number of days the volatility is taken over
volcal is the number of data items
cor1 is the standard deviation over a given range
volatility 1 should be the volatilty result

'Statistical Volatility Calculation 1




rngvol = Worksheets("config").Cells(32, 2)
volcalc = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))


Do Until Worksheets("info").Cells(rngvol + i + 1, 1).Value = ""
For i = 1 To volcalc - rngvol + 1
cor1 = WorksheetFunction.StDev(INFO.Range(INFO.Cells(2 + i, 4), INFO.Cells(rngvol + i + 1, 4)).Value)
volatility1 = cor1 * Worksheets("config").Cells(38, 2).Value
Worksheets("info").Cells(rngvol + i + 1, 8).Value = volatility1

Next i
Exit Do


Loop

 

[Norie]

Is that the entire code?

 

[ajc5382]

No, the actual code is much longer, I can post it if you'd like

 

[Norie]

Is there any code before the code you posted?

You could start by posting that instead of the whole thing.

 

[ajc5382]

I have isolated the error to a range issue. What is happening is that the code is calculating the %difference, however, at the end of the data it is using to make there calculations, there are several values that are 0. Thus, the the Overflow Error. I've tried many things but they all seem to have some sort of issue. Any suggestions as far as how to handle situations when the calculation is using bad data or how to stop the calculation once cells equal 0 or are empty

 

[Norie]

So the error is one you would get if you used the formula on a worksheet?

 

[ajc5382]

I'm guessing yes. What I'm working on now is a loop of some sort that will stop the calculation when the cell is void or is 0. This is what I have so far, I can't get it to work..

    '%Change 1


Drows = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))
 


Do
   For r = 1 To (Drows - 1)
     newval1 = Worksheets("INFO").Cells(2 + r, 2).Value
     old1 = Worksheets("INFO").Cells(1 + r, 2).Value


     percentchange1 = (newval1 - old1) / old1
     Worksheets("info").Cells(2 + r, 4).Value = percentchange1
    Next r
Loop Until Worksheets("info").Cells(1 + r, 2).Value = "0" Or ""

Cheers

 

[ajc5382]

Sorry, this is the code I'm trying to get to work.


'%Change 1


Drows = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))



Do
For r = 1 To (Drows - 1)
newval1 = Worksheets("INFO").Cells(2 + r, 2).Value
old1 = Worksheets("INFO").Cells(1 + r, 2).Value


percentchange1 = (newval1 - old1) / old1
Worksheets("info").Cells(2 + r, 4).Value = percentchange1
Next r
Loop Until Worksheets("info").Cells(1 + r, 2).Value = "0" Or ""

[/code]

 

[Mohammad Basem]

Try this,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Drows = WorksheetFunction.CountA(Sheets("INFO").Range("A:A"))
For r = 1 To (Drows - 1)
    newval1 = Worksheets("INFO").Cells(2 + r, 2).Value
    old1 = Worksheets("INFO").Cells(1 + r, 2).Value
    If old1 = 0 Then Exit For
    percentchange1 = (newval1 - old1) / old1
    Worksheets("info").Cells(2 + r, 4).Value = percentchange1
Next r
[/COLOR][/SIZE][/FONT]