Overflow Error 6 calculating % change

A

ajc5382

New Member
Joined
Jun 25, 2012
Messages
41
Hi,

I was quite a few problems using worksheetfunction.ln() to calculate % change. I decided to write more basic code to try to accomplish this but I'm getting an overflow error. Can someone tell me how to fix this.

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


   For r = 1 To (DRows - 1) '***** -1 is bc of ln property
     
     percentchange = 0
     
     newval1 = Worksheets("INFO").Cells(1 + r, 2).Value
     old1 = Worksheets("INFO").Cells(1 + r, 2).Value
    
     percentchange = (newval1 - old1) / old1
     Worksheets("info").Cells(2 + r, 3).Value = percentchange
    Next r
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
What are Drows, percentchange, newval1, old1 all dimmed as?

Also using natural logs to calculate percentage change will only ever give you an appoximate value.
 
Last edited:
Upvote 0
They were dimmed incorrectly, I went back and dimmed the appropriate variable as doubles. Thank you for you help!
 
Upvote 0
jaybee3 said:
What are Drows, percentchange, newval1, old1 all dimmed as?

Also using natural logs to calculate percentage change will only ever give you an appoximate value.
Click to expand...


I have no clue what is happening but the overflow error has returned. Oddly, the first %change calc which is exactly the same does NOT have this problem.


Code: 
'******************************************************************************'Volatility********************************************************************
'******************************************************************************


'%Change 1


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


   For R = 1 To (drows - 1) '***** -1 is bc of ln property
     
     percentchange1 = 0
     
     newval1 = Worksheets("INFO").Cells(1 + R, 2).Value
     old1 = Worksheets("INFO").Cells(1 + R, 2).Value
    
     percentchange1 = (newval1 - old1) / old1
     Worksheets("info").Cells(2 + R, 3).Value = percentchange1
    Next R


'%Change 2


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


    For R = 1 To (drows - 1)
     
     percentchange2 = 0
     
     newval2 = Worksheets("INFO").Cells(1 + R, 3).Value
     old2 = Worksheets("INFO").Cells(1 + R, 3).Value
     
     percentchange2 = (newval2 - old2) / old2
     Worksheets("info").Cells(2 + R, 3).Value = percentchange2
     Next R
 
Last edited by a moderator:
Upvote 0
It would help if you include all relevant code in question (including where you have declared/dimensioned the variables). It would also help if you could indicate on which exact line/statement the error occurs.
 
Upvote 0
There are two major errors happening here, 1 is somewhere in the Weighted Average Calc Power 2, the other is in the %CHANGE2 block of code


Code: 
Sub Calc()    
 Dim percentchange1 As Double
 Dim percentchange2 As Double
 Dim newval1 As Double
 Dim newval2 As Double
 Dim old1 As Double
 Dim old2 As Double
 Dim stdev1 As Variant
 Dim stdev2 As Variant
 
    
 
    Application.Calculation = xlCalculationManual
    
 '   Dim intLastRow As Integer
 '  'intLastRow = wks.Cells(intHeaderRow + 1, intHeaderColumn).End(xlDown).Row
    Set vHours = Config.Range("vHours")
    Set rngGAS = GAS.Range("A2:BZ1000")


        
    If Config.Cells(9, 4) = "ON" Then
        OnOff1 = 1
        Else: OnOff1 = 2
        End If
    If Config.Cells(9, 13) = "ON" Then
        OnOff2 = 1
        Else: OnOff2 = 2
        End If


NContracts = Worksheets("Config").Cells(25, 4).Value
drows = WorksheetFunction.CountA(Sheets("PWR").Range("A:A"))












'**************************************************************
'*******Colmn Headers******************************************
'**************************************************************
For X = 1 To NContracts


Worksheets("PWR").Cells(1, X + 1).Value = Worksheets("Config").Cells(12 + X, 9).Value
Worksheets("PWR").Cells(1, X + 13).Value = Worksheets("Config").Cells(12 + X, 9).Value


Worksheets("GAS").Cells(1, X + 1).Value = Worksheets("Config").Cells(12 + X, 9).Value
 


Next X








'*****************************************************************
'*****************************************************************
'WEIGHTED AVERAGE CALC FOR POWER 1********************************
'*****************************************************************
    Ncurves = Worksheets("Config").Cells(25, 4).Value
    Nrows = WorksheetFunction.CountA(Sheets("PWR").Range("A:A"))
    
    For X = 1 To Ncurves
    Worksheets("PWR").Cells(1, X + 1).Value = Worksheets("Config").Cells(12 + X, 9).Value
    Worksheets("GAS").Cells(1, X + 1).Value = Worksheets("Config").Cells(12 + X, 9).Value
    Worksheets("GAS").Cells(1, X + 13).Value = Worksheets("Config").Cells(12 + X, 9).Value
    Worksheets("GAS").Cells(1, X + 25).Value = Worksheets("Config").Cells(12 + X, 9).Value
    Next X
    
    For R = 1 To Nrows
        TotalWaverage = 0
        TotalHours = 0
        For n = 1 To Ncurves
        Waverage = 0
        vHour = Application.VLookup(PWR.Cells(1, n + 1).Value, vHours, 1 + OnOff1, False)
        Waverage = PWR.Cells(1 + R, n + 1).Value * vHour
        
            TotalWaverage = TotalWaverage + Waverage
        Next n
        For n = 1 To Ncurves
        vHour = 0
        vHour = Application.VLookup(Worksheets("PWR").Cells(1, n + 1).Value, vHours, 1 + OnOff1, False)
        
            TotalHours = TotalHours + vHour
        Next n
        Worksheets("info").Cells(1 + R, 2).Value = TotalWaverage / TotalHours
    Next R
  


    'PWR2
    Ncurves = Worksheets("Config").Cells(25, 13).Value
    Nrows = WorksheetFunction.CountA(Sheets("PWR").Range("A:A"))
    
    For X = 1 To Ncurves
    Worksheets("PWR").Cells(1, X + 13).Value = Worksheets("Config").Cells(12 + X, 18).Value
    Next X
    
    For R = 1 To Nrows
        TotalWaverage = 0
        TotalHours = 0
        For n = 1 To Ncurves
        Waverage = 0
        vHour = Application.VLookup(PWR.Cells(1, n + 13).Value, vHours, 1 + OnOff2, False)
        Waverage = PWR.Cells(1 + R, n + 13).Value * vHour
        
            TotalWaverage = TotalWaverage + Waverage
        Next n
        For n = 1 To Ncurves
        vHour = 0
        vHour = Application.VLookup(Worksheets("PWR").Cells(1, n + 13).Value, vHours, 1 + OnOff2, False)
        
            TotalHours = TotalHours + vHour
        Next n
        Worksheets("INFO").Cells(1 + R, 3).Value = TotalWaverage / TotalHours
        
    Next R
    


'**************************************************************************
'**************************************************************************
'Spread Calc***************************************************************
'**************************************************************************
'**************************************************************************
drows = WorksheetFunction.CountA(Sheets("PWR").Range("A:A"))


For R = 1 To drows


val1 = INFO.Cells(1 + R, 2).Value - INFO.Cells(1 + R, 3).Value
INFO.Cells(1 + R, 7) = val1


Next R




'****************************************************************************
'Correlation Calculation*****************************************************
'****************************************************************************




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


Do Until Worksheets("info").Cells(rngcorrel + i, 2).Value = ""
For i = 1 To correlcalc - rngcorrel + 1
      
    COR1 = WorksheetFunction.correl(INFO.Range(INFO.Cells(1 + i, 2), INFO.Cells(rngcorrel + i, 2)), INFO.Range(INFO.Cells(1 + i, 3), INFO.Cells(rngcorrel + i, 3)))
    INFO.Cells(rngcorrel + i, 6) = COR1
Next i


Exit Do


Loop






'******************************************************************************
'Volatility********************************************************************
'******************************************************************************


'%Change 1


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


   For R = 1 To (drows - 1) '***** -1 is bc of ln property
     
     percentchange1 = 0
     
     newval1 = Worksheets("INFO").Cells(1 + R, 2).Value
     old1 = Worksheets("INFO").Cells(1 + R, 2).Value
    
     percentchange1 = (newval1 - old1) / old1
     Worksheets("info").Cells(2 + R, 3).Value = percentchange1
    Next R


'%Change 2


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


    For R = 1 To drows - 1
     
     percentchange2 = 0
     
     newval2 = Worksheets("INFO").Cells(1 + R, 3).Value
     old2 = Worksheets("INFO").Cells(1 + R, 3).Value
     
     percentchange2 = (newval2 - old2) / old2
     Worksheets("info").Cells(2 + R, 3).Value = percentchange2
     Next R




'Statistical Volatility Calculation 1




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




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






'Statistical Volatility Calculation 2




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




    For i = 1 To volcalc - rngvol + 1
        cor2 = WorksheetFunction.StDev(INFO.Range(INFO.Cells(2 + i, 5), INFO.Cells(rngvol + i + 2, 5)).Value)
        volatility2 = cor2 * Worksheets("config").Cells(38, 2).Value
        Worksheets("info").Cells(rngvol + i, 9).Value = volatility2
    Next i




Application.Calculation = xlCalculationAutomatic


















'*********************************************************
'*********************************************************
End Sub
'*********************************************************
'*********************************************************
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA - Runtime Error 6 - Overflow
Replies
5
Views
334
ststern45
S
S
  • Question Question
How can i prevent overflow in this case
Replies
3
Views
333
Micron
M
J
  • Solved
VBA Code Error Unable to get the Match property of the WorksheetFunction class
Replies
8
Views
171
Jiraya_00
J
A
  • Question Question
runtime error 6 overflow, macro to create table
Replies
8
Views
520
atditiljazi
A
A
  • Solved
VBA Help!
Replies
2
Views
383
AusNewb
A

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top