Need help to solve ("Error 6" after Sometime the Macro run)

[mars91]

Hi all,
I need help to solve this Run Time Error 6 problem which I encounted after sometime the macro run.

The main objective of this code is to find ,compare data from different sheets and cells then calculate Average and Standard deviation. Below is the code.

Option Explicit
Sub ave_std()
Dim d As Object, nr&, a
Dim c(), i&, x
Dim L As Integer
Dim z As Long
Dim p As Long
Dim sht1 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("Table")
Dim area As Worksheet
Dim myLR As Long
z = 3
Do
For L = 4 To 17
If ThisWorkbook.Sheets(L).Name = sht1.Cells(z, 2).Value Then
Set area = ThisWorkbook.Sheets(L)
myLR = checkWSLR(area)
For p = 1 To myLR
If ThisWorkbook.Sheets(L).Cells(p, 5) = sht1.Cells(z, 5).Value Then
Set d = CreateObject("scripting.dictionary")
With Sheets(L)
nr = .Range("E:F").Find("*", searchorder:=xlByRows, _
    searchdirection:=xlPrevious).row
a = .Range("E:F").Resize(nr)
End With
ReDim c(1 To nr, 1 To 6)
For i = 1 To nr
    x = a(i, 1)
 
    If x = sht1.Cells(z, 5).Value Then
 
    If Not d.exists(x) Then
        d.Add x, d.Count + 1
        c(d(x), 1) = a(i, 1)
        If Not IsEmpty(a(i, 2)) Then
            c(d(x), 4) = 1
            c(d(x), 5) = a(i, 2)
            c(d(x), 6) = a(i, 2) ^ 2
        End If
    Else
        If Not IsEmpty(a(i, 2)) Then
            c(d(x), 4) = c(d(x), 4) + 1
            c(d(x), 5) = c(d(x), 5) + a(i, 2)
            c(d(x), 6) = c(d(x), 6) + a(i, 2) ^ 2
        End If
    End If
End If
Next i
For i = 1 To d.Count
    c(i, 2) = c(i, 5) / c(i, 4)
[COLOR=red][B]   c(i, 3) = ((c(i, 6) - c(i, 2) * c(i, 5)) / (c(i, 4) - 1)) ^ 0.5[/B][/COLOR]
Next i
With Sheets("Table")
.Cells(z, 7).Resize(d.Count, 3) = c
End With
sht1.Cells(z, 7) = sht1.Cells(z, 8).Value
sht1.Cells(z, 8) = sht1.Cells(z, 9).Value
sht1.Cells(z, 9) = sht1.Cells(z, 7).Value + sht1.Cells(z, 8).Value
sht1.Cells(z, 10) = (sht1.Cells(z, 9).Value / 100) * 95
End If
Next
End If
Next
z = z + 1
Loop While sht1.Cells(z, 2) <> ""
End Sub
Function checkWSLR(area As Worksheet)
Dim i As Long
i = 2
Do
i = 1 + i
Loop While area.Cells(i, 5) <> ""
checkWSLR = i
End Function

After the macro ran for sometime, It will stop at that code sentence which was highlighted in red then encounted a "Run Time Error 6". "Error 6" should be overflow error?? What i can do to solve this problem,really need help with this..

For any doubt, feel free to ask me or see my other threat : http://www.mrexcel.com/forum/showthread.php?t=562833

Thanks..

 

[GlennUK]

When this breaks, and you go into debug mode, use the Immediate window to find the value of:
(c(i, 4) - 1)
( i.e. type ? followed by that in the Immediate Window and press Enter ).

You can examine other parts of that row of code in the same way to work out what's going wrong.

 

[mars91]

Hi,
thanks for you suggestion.
I do not understand what you mean by : ( i.e. type ? followed by that in the Immediate Window and press Enter ).

You mean i will enter the debug window? I have try to find out problem from that window. However, i do not know how can i solve. Really need help.

Thanks

 

[GlennUK]

When you go into debug mode, the VBE will be displayed with various windows showing, like the code window ( where your code is ) ... which should highlight the line of code that is breaking.

To view the Immediate Window use menu command View Immediate ... this shows a window that allows you to type commands into that will be evaluated immediately. In that window type:

? (c(i, 4) - 1)

 

[mars91]

Hi GlennUK,
I have solve the problem!
Thank you.

 

[GlennUK]

I'm glad to hear it.

 

[Ruddles]

I have solve the problem!

Would you mind spending a moment or two describing what the solution was and how you discovered it?

This might help someone in a similar situation in the future who does a search on the forum. There's nothing more frustrating to find that someone has experienced the same problem as you and solved it, but then hasn't published the solution!