VBA - Runtime Error 6 - Overflow

[ststern45]

The following code gives me a Runtime Error 6 Overflow
Cell Range A2 through A26 contain the values 1 through 25

Thank you in advance.

Option Explicit

Dim Nb As Integer, NbRange As Range
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer
Dim I As Integer, J As Integer, sTxt(58906) As String


' Show Pents
Set NbRange = Range("A2:A26")
Nb = Application.WorksheetFunction.CountA(NbRange)
Range("A1").Select
I = 1
For A = 1 To Nb - 4
For B = A + 1 To Nb - 3
For C = B + 1 To Nb - 2
For D = C + 1 To Nb - 1
For E = D + 1 To Nb
sTxt(I) = ActiveCell.Offset(A, 0).Value & "--" & ActiveCell.Offset(B, 0).Value _
& "--" & ActiveCell.Offset(C, 0).Value & "--" & ActiveCell.Offset(D, 0).Value _
& "--" & ActiveCell.Offset(E, 0).Value
I = I + 1
Next E
Next D
Next C
Next B
Next A
For J = 1 To I - 1
ActiveCell.Offset(J, 3).Value = sTxt(J)
Next J
End Sub

 

[RoryA]

Which line gives the error?

 

[ststern45]

Which line gives the error?

I = I + 1

 

[RoryA]

Declare I and J as Long, not Integer.

 

[RoryA]

You'll probably find it quicker to use a 2D array and dump it to the worksheet in one go at the end:

   Dim Nb As Integer, NbRange As Range
   Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer
   Dim I As Long, sTxt(1 To 58906, 1 To 1) As String
   
   
   ' Show Pents
   Set NbRange = Range("A2:A26")
   Nb = Application.WorksheetFunction.CountA(NbRange)
   Range("A1").Select
   I = 1
   For A = 1 To Nb - 4
      For B = A + 1 To Nb - 3
         For C = B + 1 To Nb - 2
            For D = C + 1 To Nb - 1
               For E = D + 1 To Nb
                  sTxt(I, 1) = ActiveCell.Offset(A, 0).Value & "--" & ActiveCell.Offset(B, 0).Value _
                  & "--" & ActiveCell.Offset(C, 0).Value & "--" & ActiveCell.Offset(D, 0).Value _
                  & "--" & ActiveCell.Offset(E, 0).Value
                  I = I + 1
               Next E
            Next D
         Next C
      Next B
   Next A
   ActiveCell.Offset(, 3).Resize(I - 1).Value = sTxt

 

[ststern45]

Declare I and J as Long, not Integer.

Thank you Rory. That fixed the error.