[VBA] - Erreur variable tableau "Redim"
Bonjour,
J'ai une erreur lorsque je redimensionne une variable tableau.
Je me sert régulièrement des variables tableaux, je ne comprends ce qui pose problème ce cas précis...
Je joins un document Excel car ce sera beaucoup plus explicite.
L'erreur se trouve dans "Rech_data" :
With ff
b = 1
ReDim tab1(1 To lrjo - 1, 1 To b)
'tablo2 = .Range(.Cells(2, cib1), .Cells(lrjo, cib1))
For a = 2 To lrjo
b = 1
If aa(a, E1) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E1)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict1(aa(a, E1)): b = b + 1
If aa(a, E2) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E2)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1 = dict1(aa(a, E2)): b = b + 1
If aa(a, E3) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E3)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1 = dict1(aa(a, E3)): b = b + 1
If aa(a, E4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E4)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1 = dict1(aa(a, E4)): b = b + 1
If tab1(a, 1) <> "" And tab1(a, 2) <> "" And tab1(a, 3) <> "" And tab1(a, 4) <> "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1) & "x" And tab1(a, 2) & "x" & tab1(a, 3) & "x" & tab1(a, 4)
If tab1(a, 1) <> "" And tab1(a, 2) <> "" And tab1(a, 3) <> "" And tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1) & "x" And tab1(a, 2) & "x" & tab1(a, 3)
If tab1(a, 1) <> "" And tab1(a, 2) <> "" And tab1(a, 3) = "" And tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1) & "x" And tab1(a, 2)
If tab1(a, 1) <> "" And tab1(a, 2) = "" And tab1(a, 3) = "" And tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1)
Next a
b = b + 1
For a = 1 To lrjo
If tab1(a, 1) = "" Then tab1(a, b) = "": b = b + 1 Else If dict1.exists(tab1(a, 1)) Then tab1(a, b) = dict1(tab1(a, 1)): b = b + 1
Next a
End WithC'est un code plutôt conséquent, mais en exécutant l'UF qui s'ouvre quand on va sur la 2nde feuille, vous devriez avoir directement l'erreur.
Je vous remercie de votre attention,
Bonne journée !
bonjour,
regarde ton code
If aa(a, E1) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E1)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict1(aa(a, E1)): b = b + 1
If aa(a, E2) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E2)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1 = dict1(aa(a, E2)): b = b + 1
If aa(a, E3) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E3)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1 = dict1(aa(a, E3)): b = b + 1
If aa(a, E4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = b + 1 Else If dict1.exists(aa(a, E4)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1 = dict1(aa(a, E4)): b = b + 1tu oublies de préciser les indices de tableau de tab1 pour E2,E3 et E4 dans ton else if, ce faisant ton tableau tab1 devient une simple variable sans dimension sur laquelle un redim preserve ne peut pas fonctionner.
EDIT : Voir dernier post
Bonjour,
Merci beaucoup pour votre aide ! J'avais beau reprendre le code encore et encore, ça m'avait échappé...
Maintenant, je n'ai plus d'erreur. J'ai finalisé la macro, elle donne tout l'air de fonctionner comme souhaité. Sauf que le résultat n'apparaît pas.
Le travail de cette macro consiste à compléter des colonnes en fonction d'informations contenues dans des dictionnaires (générés à partir d'une base de données).
Je remplis alors une variable tableau à partir des résultats de ces recherches, puis je demande à coller cette variable tableau dans mon tableur, après la dernière colonne.
Malheureusement, le résultat qui est donné correspond uniquement à la première colonne (de la variable tableau). Pourtant, lorsque je regarde le fonctionnement de la macro en pas à pas, cette dernière semble remplir la variable tableau correctement.
Alors je me dit que peut-être cela vient de la manière dont je demande à Excel de coller le resultat :
sve = dernière colonne ; lrjo = dernière ligne ; tab1 = variable tableau (qui fait 25 colonne en théorie)
Soit
.Cells(2, sve + 1).Resize(lrjo - 1, 25) = tab1
Soit
.Range(.Cells(2, sve + 1), .Cells(lrjo - 1, lcjo)) = tab1Je joins à nouveau mon document Excel si vous souhaitez regarder.
Dans tous les cas, merci encore pour votre réponse, vous m'avez aidé à résoudre le problème initial.
Edit ça fonctionne
Ci-après le code (un peu énorme...) mis à jour car le service pour ajouter un fichier n'est plus disponible.
Public Sub rech_data()
Dim a%, ff As Worksheet, e As Byte, tab1, aa, lrtc&, chk41 As Byte, sve%
Call Set_Feuilles
'If chk40 = 0 Or chk40 = 2 Then Set ff = Worksheets("JOINTURE")
'If chk40 = 1 Then Set ff = Worksheets("NATURA_2000")
Set ff = Worksheets("JOINTURE")
With ff
e = 0: chk41 = 0: lrjo = .UsedRange.SpecialCells(xlCellTypeLastCell).Row: lcjo = .Cells(1, .Columns.Count).End(xlToLeft).Column: sve = lcjo
ReDim tab1(1 To lrjo, 1 To 25)
For a = 1 To 25
If e < 5 Then e = e + 1 Else e = 1
If a < 5 Then tab1(1, a) = "CORINE_" & e
If a = 5 Then tab1(1, a) = "CODE_CORINE"
If a > 5 And a < 10 Then tab1(1, a) = "EUR27_" & e
If a = 10 Then tab1(1, a) = "CODE_EUR27"
If a > 10 And a < 15 Then tab1(1, a) = "COMM/PRIO_" & e
If a = 15 Then tab1(1, a) = "HAB_COMMUNAUTAIRE"
If a > 15 And a < 20 Then tab1(1, a) = "ZH_" & e
If a = 20 Then tab1(1, a) = "HAB_ZONE_HUMIDE"
If a > 20 And a < 25 Then tab1(1, a) = "PVF_" & e
If a = 25 Then tab1(1, a) = "PRODROME_VEGET"
Next a
.Cells(1, lcjo + 1).Resize(1, 25) = tab1: lcjo = .Cells(1, .Columns.Count).End(xlToLeft).Column
aa = .Range(.Cells(1, 1), .Cells(lrjo, lcjo))
End With
Call dcl_var_jo
Call dcl_tc 'temporaire
Dim tablo1, tablo2, dict1 As Object, dict2 As Object, dict3 As Object, dict4 As Object, dict5 As Object, b%
Set dict1 = CreateObject("scripting.dictionary"): Set dict2 = CreateObject("scripting.dictionary")
Set dict3 = CreateObject("scripting.dictionary"): Set dict4 = CreateObject("scripting.dictionary")
Set dict5 = CreateObject("scripting.dictionary")
With Tc
lrtc = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
tablo1 = .Range(.Cells(2, 1), .Cells(lrtc, tcdz))
For a = 2 To UBound(tablo1)
dict1(tablo1(a, tcce)) = tablo1(a, tccc): dict2(tablo1(a, tcce)) = tablo1(a, tccr)
dict3(tablo1(a, tcce)) = tablo1(a, tccp): dict4(tablo1(a, tcce)) = tablo1(a, tczh)
dict5(tablo1(a, tcce)) = tablo1(a, tcpv)
Next a
End With
With ff
b = 1
ReDim tab1(1 To lrjo - 1, 1 To b)
For a = 2 To lrjo - 1
b = 1
If aa(a, E1) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 2 Else If dict1.exists(aa(a, E1)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict1(aa(a, E1)): b = 2 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 2
If aa(a, E2) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 3 Else If dict1.exists(aa(a, E2)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict1(aa(a, E2)): b = 3 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 3
If aa(a, E3) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 4 Else If dict1.exists(aa(a, E3)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict1(aa(a, E3)): b = 4 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 4
If aa(a, E4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 5 Else If dict1.exists(aa(a, E4)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict1(aa(a, E4)): b = 5 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 5
If tab1(a, 1) <> "" And tab1(a, 2) <> "" And tab1(a, 3) <> "" And tab1(a, 4) <> "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1) & "x" & tab1(a, 2) & "x" & tab1(a, 3) & "x" & tab1(a, 4)
If tab1(a, 1) <> "" And tab1(a, 2) <> "" And tab1(a, 3) <> "" And tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1) & "x" & tab1(a, 2) & "x" & tab1(a, 3)
If tab1(a, 1) <> "" And tab1(a, 2) <> "" And tab1(a, 3) = "" And tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1) & "x" & tab1(a, 2)
If tab1(a, 1) <> "" And tab1(a, 2) = "" And tab1(a, 3) = "" And tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 1)
b = 6
If tab1(a, 1) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 7 Else If dict2.exists(tab1(a, 1)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict2(tab1(a, 1)): b = 7 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 7
If tab1(a, 2) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 8 Else If dict2.exists(tab1(a, 2)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict2(tab1(a, 2)): b = 8 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 8
If tab1(a, 3) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 9 Else If dict2.exists(tab1(a, 3)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict2(tab1(a, 3)): b = 9 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 9
If tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 10 Else If dict2.exists(tab1(a, 4)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict2(tab1(a, 4)): b = 10 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 10
If tab1(a, 6) <> "" And tab1(a, 7) <> "" And tab1(a, 8) <> "" And tab1(a, 9) <> "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 6) & "x" & tab1(a, 7) & "x" & tab1(a, 8) & "x" & tab1(a, 9)
If tab1(a, 6) <> "" And tab1(a, 7) <> "" And tab1(a, 8) <> "" And tab1(a, 9) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 6) & "x" & tab1(a, 7) & "x" & tab1(a, 8)
If tab1(a, 6) <> "" And tab1(a, 7) <> "" And tab1(a, 8) = "" And tab1(a, 9) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 6) & "x" & tab1(a, 7)
If tab1(a, 6) <> "" And tab1(a, 7) = "" And tab1(a, 8) = "" And tab1(a, 9) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 6)
b = 11
If tab1(a, 6) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 12 Else If dict3.exists(tab1(a, 6)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict3(tab1(a, 6)): b = 12 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 12
If tab1(a, 7) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 13 Else If dict3.exists(tab1(a, 7)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict3(tab1(a, 7)): b = 13 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 13
If tab1(a, 8) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 14 Else If dict3.exists(tab1(a, 8)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict3(tab1(a, 8)): b = 14 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 14
If tab1(a, 9) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 15 Else If dict3.exists(tab1(a, 9)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict3(tab1(a, 9)): b = 15 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 15
If tab1(a, 11) <> "" And tab1(a, 12) <> "" And tab1(a, 13) <> "" And tab1(a, 14) <> "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 11) & "x" & tab1(a, 12) & "x" & tab1(a, 13) & "x" & tab1(a, 14)
If tab1(a, 11) <> "" And tab1(a, 12) <> "" And tab1(a, 13) <> "" And tab1(a, 14) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 11) & "x" & tab1(a, 12) & "x" & tab1(a, 13)
If tab1(a, 11) <> "" And tab1(a, 12) <> "" And tab1(a, 13) = "" And tab1(a, 14) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 11) & "x" & tab1(a, 12)
If tab1(a, 11) <> "" And tab1(a, 12) = "" And tab1(a, 13) = "" And tab1(a, 14) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 11)
b = 16
If tab1(a, 1) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 17 Else If dict4.exists(tab1(a, 1)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict4(tab1(a, 1)): b = 17 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 17
If tab1(a, 2) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 18 Else If dict4.exists(tab1(a, 2)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict4(tab1(a, 2)): b = 18 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 18
If tab1(a, 3) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 19 Else If dict4.exists(tab1(a, 3)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict4(tab1(a, 3)): b = 19 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 19
If tab1(a, 4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 20 Else If dict4.exists(tab1(a, 4)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict4(tab1(a, 4)): b = 20 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 20
If tab1(a, 16) <> "" And tab1(a, 17) <> "" And tab1(a, 18) <> "" And tab1(a, 19) <> "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 16) & "x" & tab1(a, 17) & "x" & tab1(a, 18) & "x" & tab1(a, 19)
If tab1(a, 16) <> "" And tab1(a, 17) <> "" And tab1(a, 18) <> "" And tab1(a, 19) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 16) & "x" & tab1(a, 17) & "x" & tab1(a, 18)
If tab1(a, 16) <> "" And tab1(a, 17) <> "" And tab1(a, 18) = "" And tab1(a, 19) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 16) & "x" & tab1(a, 17)
If tab1(a, 16) <> "" And tab1(a, 17) = "" And tab1(a, 18) = "" And tab1(a, 19) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 16)
b = 21
If aa(a, E1) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 22 Else If dict5.exists(aa(a, E1)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict5(aa(a, E1)): b = 22 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 22
If aa(a, E2) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 23 Else If dict5.exists(aa(a, E2)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict5(aa(a, E2)): b = 23 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 23
If aa(a, E3) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 24 Else If dict5.exists(aa(a, E3)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict5(aa(a, E3)): b = 24 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 24
If aa(a, E4) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "": b = 25 Else If dict5.exists(aa(a, E4)) Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = dict5(aa(a, E4)): b = 25 Else ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = "-": b = 25
If tab1(a, 21) <> "" And tab1(a, 22) <> "" And tab1(a, 23) <> "" And tab1(a, 24) <> "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 21) & "x" & tab1(a, 22) & "x" & tab1(a, 23) & "x" & tab1(a, 24)
If tab1(a, 21) <> "" And tab1(a, 22) <> "" And tab1(a, 23) <> "" And tab1(a, 24) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 21) & "x" & tab1(a, 22) & "x" & tab1(a, 23)
If tab1(a, 21) <> "" And tab1(a, 22) <> "" And tab1(a, 23) = "" And tab1(a, 24) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 21) & "x" & tab1(a, 22)
If tab1(a, 21) <> "" And tab1(a, 22) = "" And tab1(a, 23) = "" And tab1(a, 24) = "" Then ReDim Preserve tab1(1 To lrjo - 1, 1 To b): tab1(a, b) = tab1(a, 21)
Next a
'.Cells(2, sve + 1).Resize(lrjo - 1, 25) = tab1
.Range(.Cells(2, sve + 1), .Cells(lrjo - 1, lcjo)) = tab1
End With
End SubA plus tard !
Ah je pense avoir trouvé, il ne faut pas que j'utilise Redim Preserve dans mon cas, puisque b retourne à une valeur inférieure régulièrement, le redimensionnement selon "b" lorsqu'il est inférieur fait disparaître les colonnes qui ne sont plus prises en compte.
Il suffit de dimensionner tab1 avec 25 colonnes au départ et ne plus redimensionner.
Bonne journée !
!