View original (French)

VBA Excel: Solve capacity issue.

Solved
Juan Pedro -  
 Kunzi -
Hello to all the Excel VBA pros and the others.

I have a little problem (type error "6") that prevents one of my codes from completing even though there had never been an issue until now... Strange, strange...

Do you have an explanation for this sudden problem? Do you have any leads to resolve this overflow issue? In any case, thank you for taking the time to look into my problem!!

Dim Tableau1() As Integer
Dim x1 As Integer, y1 As Integer
Dim i1 As Integer, j1 As Integer

x1 = 1465
y1 = 119
'Resize the array from row 3 to x and from column 2 to y

ReDim Tableau1(2 To x1, 3 To y1)

'Fill the array values by adding the cells from the same range on 2 sheets

For i1 = 2 To x1
For j1 = 3 To y1
Tableau1(i1, j1) = Workbooks("Fichier Personnel X.xls").Sheets("Statistiques").Cells(i1, j1).Value + Workbooks("Classeur Commun X").Sheets("Statistiques").Cells(i1, j1).Value
Next j1
Next i1

'Restore the array values in sheet3

With Workbooks("Classeur Commun X.xls").Sheets("Statistiques")
.Range("C2:DO1465") = Tableau1
End With
Workbooks("Fichier Personnel X.xls").Sheets("Statistiques").Range("C2:DO1465").ClearContents

Configuration: Windows XP / Internet Explorer 6.0

6 réponses

Réponse 1 / 6
pilas31 Posted messages 1878 Status Contributeur 648
 
Hello,

Just a note, the overflow is occurring on the array because it is set to "integer", meaning it is limited to 32767. It only takes one of the additions to exceed this limit to receive that message.

Perhaps it would be better to declare it as double or long:

Dim Tableau1() As Double
or
Dim Tableau1() As Long

Right?

See you later

Best regards,
7
Juan Pedro
 
It works! Well done Pilas!!! Thank you very much!!
0
Kunzi
 
Hello. That works. Well done Pilas. That takes a weight off my shoulders.
0