[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
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:
Dim tempval As Double
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)))
tempval = tempval * 11.85
tempval = tempval * cell.Offset(0, 7).Value
tempval = tempval / cell.Offset(0, 5).Value
tempval = tempval / 1000
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
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
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:
if isempty(cell) = false then
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
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
1 Attachment(s)
Re: [RESOLVED] Runtime Error 6 Overflow
Quote:
Originally Posted by
westconn1
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
1 Attachment(s)
Re: [RESOLVED] Runtime Error 6 Overflow
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
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
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
1 Attachment(s)
Re: [RESOLVED] Runtime Error 6 Overflow
Quote:
Originally Posted by
westconn1
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.Attachment 161599
1 Attachment(s)
Re: [RESOLVED] Runtime Error 6 Overflow
Quote:
Originally Posted by
westconn1
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 -Attachment 161603
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
Re: [RESOLVED] Runtime Error 6 Overflow
Quote:
Originally Posted by
westconn1
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
Re: [RESOLVED] Runtime Error 6 Overflow
you should not put your email on the forum, use PM