Excel vba Run-time error 6 overflow

Indika Bandara · May 20, 2011

[FONT=&quot]I have a problem with the macro in the attached file. I receive an overflow error and I can’t seem to debug it. Though the debugger shows the error in the line “total_qi_score = sum_product / sum_ques_answered”, I know that the error is coming because of the code higher up – [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]srch_string = CStr(ques_list.Cells(i, 1).Value)[/FONT]
[FONT=&quot] myPos = InStr(1, srch_string, "(")[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If myChar = 0 Then GoTo line1[/FONT]

[FONT=&quot][/FONT]

[FONT=&quot][/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]--------------------------------------------------------------[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]Sub Button1_Click()

Dim ques_list As Range
Dim i, j, k As Long
Dim no_ques As Long
Dim srch_string
Dim report_ptr As Long
Dim rgLast As Range
Dim lLastRow As Long
Dim lLastCol As Long
Dim vg, g, p, vp, flag_ques_found, counti As Long
Dim vg_temp, g_temp, p_temp, vp_temp, ques_ptr As Long
Dim qi_score_dept, qi_score_ques As Long
Dim sum_product As Long
Dim sum_ques_answered As Long
Dim total_qi_score As Long
Dim flag_do_not_include As Long
Dim myChar

flag_do_not_include = 0
sum_product = 0
sum_ques_answered = 0
total_qi_score = 0

report_ptr = 3
ques_ptr = 4

'change range definition if departments are added
Set dept_name = Sheets("Question Count - DNT").Range("A1:A60")

Worksheets("Data Dump 2").Activate
'change range definition if questions are added
Set ques_list = Range("A1:A350")

For i = 1 To ques_list.Rows.Count
'check for non blank row
If ques_list.Cells(i, 1) <> " " Then
srch_string = CStr(ques_list.Cells(i, 1).Value)
myPos = InStr(1, srch_string, "(")

If myChar = 0 Then GoTo line1
qi_score_dept = ques_list.Cells(i, 8) - 100
Worksheets("Question Count - DNT").Activate
For j = 1 To dept_name.Rows.Count
If srch_string = dept_name.Cells(j, 1).Value Then
no_ques = dept_name.Cells(j, 2).Value
If ques_ptr = report_ptr Then
ques_ptr = report_ptr + 1
End If
flag_do_not_include = 0
If dept_name.Cells(j, 3).Value = 1 Then
flag_do_not_include = 1
End If
Worksheets("Report").Activate
Worksheets("Report").Cells(report_ptr, 1).Value = srch_string
Worksheets("Report").Cells(report_ptr, 2).Value = no_ques
Worksheets("Report").Cells(report_ptr, 9).Value = qi_score_dept

Rows(report_ptr).Font.Bold = True
vg = 0
g = 0
p = 0
vp = 0
Worksheets("Data Dump 2").Activate
For k = (i + 1) To (i + no_ques)
ques_string = ""
ques_string = Cells(k, 1).Value
Worksheets("Question Count - DNT").Activate
flag_ques_found = 0
For counti = 1 To dept_name.Rows.Count
If ques_string = dept_name.Cells(counti, 1).Value Then
flag_ques_found = 1
End If
Next counti

If flag_ques_found = 0 Then

Worksheets("Data Dump 2").Activate
vg_temp = 0
g_temp = 0
p_temp = 0
vp_temp = 0
vg_temp = (Cells(k, 3).Value / 100) * Cells(k, 2).Value
vg = vg + vg_temp
g_temp = (Cells(k, 4).Value / 100) * Cells(k, 2).Value
g = g + g_temp
p_temp = (Cells(k, 5).Value / 100) * Cells(k, 2).Value
p = p + p_temp
vp_temp = (Cells(k, 6).Value / 100) * Cells(k, 2).Value
vp = vp + vp_temp
qi_score_ques = Cells(k, 8).Value - 100

Worksheets("Report").Activate

Cells(ques_ptr, 1).Value = ques_string
Cells(ques_ptr, 5).Value = vg_temp
Cells(ques_ptr, 6).Value = g_temp
Cells(ques_ptr, 7).Value = p_temp
Cells(ques_ptr, 8).Value = vp_temp
Cells(ques_ptr, 9).Value = qi_score_ques
vg_temp = 0
g_temp = 0
p_temp = 0
vp_temp = 0
ques_ptr = ques_ptr + 1
End If
If flag_ques_found = 1 Then
Worksheets("Report").Activate
ActiveSheet.Range(Cells(report_ptr + 1, 1), Cells(ques_ptr - 1, 1)).Select
Selection.Rows.Group
Exit For
End If
Worksheets("Data Dump 2").Activate
Next k
Worksheets("Report").Activate
Cells(report_ptr, 5).Value = vg
Cells(report_ptr, 6).Value = g
Cells(report_ptr, 7).Value = p
Cells(report_ptr, 8).Value = vp
If flag_do_not_include = 0 Then
sum_product = sum_product + (Cells(report_ptr, 4).Value * Cells(report_ptr, 9).Value)
sum_ques_answered = sum_ques_answered + Cells(report_ptr, 4).Value
End If
ActiveSheet.Range(Cells(report_ptr + 1, 1), Cells(ques_ptr - 1, 1)).Select
Selection.Rows.Group
report_ptr = ques_ptr
Exit For
End If
Worksheets("Question Count - DNT").Activate
Next j
End If
Worksheets("Data Dump 2").Activate
line1:
Next i
Worksheets("Report").Activate
total_qi_score = sum_product / sum_ques_answered
Cells(2, 9).Value = total_qi_score
ActiveSheet.Outline.ShowLevels rowlevels:=1
End Sub

[/FONT]

Ruddles · May 20, 2011

What are the values in sum_product and sum_ques_answered when the code errors?

Indika Bandara · May 22, 2011

[FONT=&quot]hi [/FONT]Ruddles,

thank you for ur time.
[FONT=&quot]
This is a runtime error i got.
Values we gave to sum_product and sum_ques_answered are as bellow

sum_product = sum_product + (Cells(report_ptr, 4).Value * Cells(report_ptr, 9).Value)
sum_ques_answered = sum_ques_answered + Cells(report_ptr, 4).Value

FYI if i comment
' myPos = InStr(1, srch_string, "(")'

' If myChar = 0 Then GoTo line1'

the program is working ,

[/FONT][FONT=&quot]

[/FONT]

Norie · May 22, 2011

One reason for an overflow error is if you try and divide by zero.

Have you checked that?

If that's the case then you need to trace how sum_ques_answered is getting it's value.

You would probably start that by looking at all the values here.

Code:

sum_product = sum_product + (Cells(report_ptr, 4).Value * Cells(report_ptr, 9).Value) 

sum_ques_answered = sum_ques_answered + Cells(report_ptr, 4).Value

One problem could be that without a worksheet reference Cells isn't looking at the correct worksheet.

You actually seem to use worksheet references elsewhere for Cells and Range.

So it should be straightforward to add them elsewhere.

The If InStr.... Goto Line1 could be causing the problem because if the code does jump to Line1 it skips a fair amount of code.

Ruddles · May 23, 2011

Indika Bandara said:
[FONT=&quot]Values we gave to sum_product and sum_ques_answered are as bellow [/FONT]

[FONT=&quot]sum_product = sum_product + (Cells(report_ptr, 4).Value * Cells(report_ptr, 9).Value) [/FONT]
[FONT=&quot]sum_ques_answered = sum_ques_answered + Cells(report_ptr, 4).Value [/FONT]

No, I meant: what are the actual numerical values of those variables? As Norie says, they probably don't contain what you think they do. When the error occurs, mouse over the variable name and VBA should tell you what their values are. If that doesn't work, hit Ctrl-G to open the VBA Immediate window and type ?sum_product followed by Enter and then ?sum_ques_answered followed by Enter. Are the values what you expected?

Then I agree with Norie's other suggestions:-

References to worksheet values should be fully-qualified - that way they won't be capable of being misunderstood by VBA as they may be if you have more than one worksheet in your workbook or if you have more than one workbook open.
Remove On Error directives during testing and development unless you are using them to trap errors which are difficult to detect in advance.
Step through your code and make sure execution is going where you expect it to go, monitoring the values of your variables as you go.

Indika Bandara · May 23, 2011

Thank you Ruddles & Norie for your Time and advice !

The values in sum_product and sum_ques_answered

?sum_product
0

?sum_ques_answered
0

and happy news its working . after chk the zero's.

Thank you Ruddle & Norie .

Excel vba Run-time error 6 overflow

Indika Bandara

New Member

Excel Facts

Ruddles

Well-known Member

Indika Bandara

New Member

Norie

Well-known Member

Ruddles

Well-known Member

Indika Bandara

New Member

Similar threads

Forum statistics

Share this page

Excel vba Run-time error 6 overflow

Indika Bandara

New Member

Excel Facts

Ruddles

Well-known Member

Indika Bandara

New Member

Norie

Well-known Member

Ruddles

Well-known Member

Indika Bandara

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock