Results 1 to 16 of 16

Thread: [RESOLVED] Runtime Error 6 Overflow

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2010
    Posts
    30

    Resolved [RESOLVED] Runtime Error 6 Overflow

    I have the following code that keeps giving me a runtime error 6 "overflow". I have read that it is usually caused by too much information being processed a the same time, but I am not sure how to fix it.

    Any help would be appreciated.


    Code:
    For Each Cell In Sheets("CHLOROPHYLLS").Range("A12:A45")
    
       
       If IsEmpty(ActiveCell) = False Then
            Cell.Offset(0, 12).Value = (((11.85 * (Abs(Cell.Offset(0, 8).Value - Cell.Offset(0, 11).Value))) - _
            (1.54 * (Abs(Cell.Offset(0, 8).Value - Cell.Offset(0, 10).Value))) - (0.08 * (Abs _
            (Cell.Offset(0, 8).Value - Cell.Offset(0, 9).Value)))) * Cell.Offset(0, 7).Value) / (Cell.Offset(0, 5).Value / 1000)
            
        Else
          End If
            
      Next

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Runtime Error 6 Overflow

    it means that the value returned from some part of your calculation is too large for the default TYPE that will contain the value

    break the calculation into sections to find which part is is returning the error
    also it may only be happening in certain cells, so you need to find which cells (values) cause the error

    try like
    vb Code:
    1. Dim tempval As Double
    2. tempval = Abs(cell.Offset(0, 8).Value - cell.Offset(0, 11).Value) - (1.54 * (Abs(cell.Offset(0, 8).Value - cell.Offset(0, 10).Value))) - (0.08 * (Abs(cell.Offset(0, 8).Value - cell.Offset(0, 9).Value)))
    3. tempval = tempval * 11.85
    4. tempval = tempval * cell.Offset(0, 7).Value
    5. tempval = tempval / cell.Offset(0, 5).Value
    6. tempval = tempval / 1000
    7. Cell.Offset(0, 12).Value = tempval
    find which line errors
    if the first line errors break it down further
    if need be change tempval to currency type
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Nov 2010
    Posts
    30

    Re: Runtime Error 6 Overflow

    WestConn1,

    Thank you for your help. I had to change the code a bit for it to calculate the way I wanted. The code below is what finally stopped the overflow issue.

    Code:
    For Each cell In Sheets("CHLOROPHYLLS").Range("A12:A45")
    
    cell.Select
    If IsEmpty(ActiveCell) = False Then
    Dim tempval As Double
    tempval = (11.85 * (Abs(cell.Offset(0, 8).Value - cell.Offset(0, 11).Value))) - _
    (1.54 * (Abs(cell.Offset(0, 8).Value - cell.Offset(0, 10).Value))) - _
    (0.08 * (Abs(cell.Offset(0, 8).Value - cell.Offset(0, 9).Value)))
    tempval = tempval * cell.Offset(0, 7).Value
    tempval = tempval / (cell.Offset(0, 5).Value / 1000)
    cell.Offset(0, 12).Value = tempval
    
    End If
    
         
    Next

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Runtime Error 6 Overflow

    take out the cell.select, as it is not required and slows down the process
    change to
    vb Code:
    1. if isempty(cell) = false then
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Unhappy Re: Runtime Error 6 Overflow

    Dear Team , can you please suggest where it is going wrong .. facing same error 6
    'TE Forecasting code
    For j = dataStartCol + seasonality To forecastEndCol - 2
    If (j <= dataEndCol - 1) Then
    TE_LvlArray(j) = alpha * dataArray(i, j) / TE_SeasonArray(j - seasonality) + (1 - alpha) * (TE_LvlArray(j - 1) + TE_TrendArray(j - 1))
    TE_TrendArray(j) = beta * (TE_LvlArray(j) - TE_LvlArray(j - 1)) + (1 - beta) * TE_TrendArray(j - 1)
    TE_SeasonArray(j) = gamma * dataArray(i, j) / TE_LvlArray(j) + (1 - gamma) * TE_SeasonArray(j - seasonality)
    forecast2M(rowToadd + 2, j + 2) = (TE_LvlArray(j) + TE_TrendArray(j) * 2) * TE_SeasonArray(j + 2 - seasonality)
    Else
    If (j <= forecastEndCol - 2) Then
    forecast2M(rowToadd + 2, j + 2) = (TE_LvlArray(dataEndCol - 1) + TE_TrendArray(dataEndCol - 1) * (j - dataEndCol + 3)) * TE_SeasonArray(j + 2 - seasonality)
    End If
    End If
    If (forecast2M(rowToadd + 2, j + 2) < 0) Then
    forecast2M(rowToadd + 2, j + 2) = 0
    End If
    Next j
    End If

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Runtime Error 6 Overflow

    use code tags when posting code, to make it more understandable

    you have not posted your dimensioning of variables for us to see what types they are

    can you specify which line the error occurs, also the values in the arrays and other variables when the error occurs
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Post Re: [RESOLVED] Runtime Error 6 Overflow

    Quote Originally Posted by westconn1 View Post
    use code tags when posting code, to make it more understandable

    you have not posted your dimensioning of variables for us to see what types they are

    can you specify which line the error occurs, also the values in the arrays and other variables when the error occurs
    Thanks for your reply.
    Please refer below attachement and help to sugest -


    When trying to calculate TE - 'TE Forecasting code
    For j = dataStartCol + seasonality To forecastEndCol - 2
    If (j <= dataEndCol - 1) Then
    TE_LvlArray(j) = alpha * dataArray(i, j) / TE_SeasonArray(j - seasonality) + (1 - alpha) * (TE_LvlArray(j - 1) + TE_TrendArray(j - 1))
    error comes as a overflow
    Attached Files Attached Files

  8. #8
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Re: [RESOLVED] Runtime Error 6 Overflow

    Name:  error 6.jpg
Views: 17850
Size:  26.8 KB

  9. #9
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Re: [RESOLVED] Runtime Error 6 Overflow

    Hi Westconn1,
    Is attached word file is helpful ?

    Looking for your help to get the error resolved .

    Thanks & Regards,
    Hemant

  10. #10
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Unhappy Re: [RESOLVED] Runtime Error 6 Overflow

    Hi Westconn1,
    Is attached word file is helpful ?

    Looking for your help to get the error resolved .

    Thanks & Regards,
    Hemant

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Runtime Error 6 Overflow

    you can test this to see if it fixes the error, of course the same or someother error can occur on a later row
    Code:
    TE_LvlArray(j) = clng(alpha) *clng(dataArray(i, j)) /clng( TE_SeasonArray(j - seasonality) + (1 - clng(alpha)) * (clng(TE_LvlArray(j - 1)) + clng(TE_TrendArray(j - 1)))
    not all the type casting may be needed, but probably will not hurt anything
    NOTE if the values may contain decimal cdbl should be use in place of clng

    for any further help, you should attach a sample workbook (zip first) to a post
    make sure it contains the code and enough sample data for the error to occur
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Re: [RESOLVED] Runtime Error 6 Overflow

    Quote Originally Posted by westconn1 View Post
    you can test this to see if it fixes the error, of course the same or someother error can occur on a later row
    Code:
    TE_LvlArray(j) = clng(alpha) *clng(dataArray(i, j)) /clng( TE_SeasonArray(j - seasonality) + (1 - clng(alpha)) * (clng(TE_LvlArray(j - 1)) + clng(TE_TrendArray(j - 1)))
    not all the type casting may be needed, but probably will not hurt anything
    NOTE if the values may contain decimal cdbl should be use in place of clng

    for any further help, you should attach a sample workbook (zip first) to a post
    make sure it contains the code and enough sample data for the error to occur
    Thanks westconn1,

    Now error 11 coming as division by 0 , please refer below attached screen shot.Name:  Capture error 11 division by 0.jpg
Views: 17239
Size:  22.6 KB

  13. #13
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Re: [RESOLVED] Runtime Error 6 Overflow

    Quote Originally Posted by westconn1 View Post
    you can test this to see if it fixes the error, of course the same or someother error can occur on a later row
    Code:
    TE_LvlArray(j) = clng(alpha) *clng(dataArray(i, j)) /clng( TE_SeasonArray(j - seasonality) + (1 - clng(alpha)) * (clng(TE_LvlArray(j - 1)) + clng(TE_TrendArray(j - 1)))
    not all the type casting may be needed, but probably will not hurt anything
    NOTE if the values may contain decimal cdbl should be use in place of clng

    for any further help, you should attach a sample workbook (zip first) to a post
    make sure it contains the code and enough sample data for the error to occur
    Thanks westconn1,

    Please find attached worksheet for reference ... to run it start from console sheet & step 3 directly -Name:  Capture in console start from Step 3 to run.jpg
Views: 17027
Size:  53.1 KB

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Runtime Error 6 Overflow

    the attachment is an empty zip.......
    drag the workbook into it and update the attachment

    the error indicates that TE_LvlArray(j) is zero, try changing all clng to cdbl for the previous error
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15
    New Member
    Join Date
    Sep 2018
    Posts
    8

    Re: [RESOLVED] Runtime Error 6 Overflow

    Quote Originally Posted by westconn1 View Post
    the attachment is an empty zip.......
    drag the workbook into it and update the attachment

    the error indicates that TE_LvlArray(j) is zero, try changing all clng to cdbl for the previous error
    Hi Westconn1,


    Sorry for inconvenience caused .. Not able to upload file as exceeding 10MB can you please share your email Id so that forward excel from my Gmail I'd .

    Thanks & Regards,
    Hemant
    hemant16baviskar@gmail.com
    9834247771

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Runtime Error 6 Overflow

    you should not put your email on the forum, use PM
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width