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
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
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
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
take out the cell.select, as it is not required and slows down the process
change to
vb Code:
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
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
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
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.
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 -
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
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 .
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