Range et redimensionner un tableau

Bonjour,

Je m'arrache les cheveux sur sur une ligne de code. Un oeil nouveau pourrait surement me débloquer la situation.

Je travaille sur un fichier document unique.

Pour alimenter la feuille plan de prévention, je vais lire dans les feuilles unités les données.

Pour chaque feuille unité, j'affecte les valeurs dans un tableau T

Selon une condition, j'affecte la ligne du tableau T dans un nouveau tableau T2 à l'aide de la ligne application.index

Tout se gâte quand je veux ecrire le tableau T2 dans la feuille plan de prévention.

Le code qui dysfonctionne est celui-ci: La ligne f.Range("A6")... assurément dysfonctionne

Option Base 1
Sub MisàJourPlanActionPrevention()
Dim T As Variant, T2() As Variant
Dim f As Worksheet, Coef As Integer, c%, i%, j%

Set f = Sheets("Plan de prévention")
Application.EnableEvents = False
'efface le contenu de la feuille plan de prévention
f.Range("A6:O1500").ClearContents
'Coefficient d'évaluation du risque
Coef = f.[D3].Value
'boucle sur les feuilles U..
For i = 1 To Sheets.Count
   If (Left(Sheets(i).Name, 1) = "U" Or Left(Sheets(i).Name, 1) = "u") And Sheets(i).[B4] <> "" Then
      With Sheets(i)
      .Unprotect 'déprotection de la feuille
      T = Sheets(i).Range("B4:P" & .Range("B" & Rows.Count).End(xlUp).Row)
         'boucle sur les lignes des tableaux T
         For j = LBound(T, 1) To UBound(T, 1)
            If T(j, 10) >= Coef Then
               c = c + 1
               ReDim Preserve T2(c)
               Debug.Print Sheets(i).Name
               T2(c) = Application.Index(T, j) 'affecte la ligne
            End If
         Next j
      End With
   End If
Next i
'ecriture du tableau dans la feuille plan de prévention
f.Range("A6").Resize(UBound(T2), UBound(T, 2)) = Application.Transpose(Application.Transpose(T2))
Set f = Nothing
End Sub

Merci pour le coup de main.

Cordialement

Bonsoir ddetp88

C'est cette ligne qui cloche

T2(c) = Application.Index(T, j) 'affecte la ligne

Regarde ce que vaut T2, ou plutôt T2(1) et T2(2)

Dans ton cas, on ne pas pas retranscrire T2 comme ceci :

= Application.Transpose(Application.Transpose(T2))

il faudrait boucler sur tous ses éléments et les retranscrire un par un

Pas poussé l'analyse plus loin

klin89

Bonsoir,

A mon avis, tu devrais tester les dimensions de T2... avant ton affectation qui me laisse très dubitatif, de même que l'utilisation d'INDEX !

Ce que j'aurais fait :

  • me passer de T...
  • opérer avec T2(14, c) [NB- Je n'utilise jamais l'Option Base 1...]
  • affecter chaque ligne de ce tableau, élément par élément...
  • affecter le tableau transposé à la fin.
C'est classique mais me paraît toujours plus sûr !

Je passe sur les autres détails qui me font toujours un peu tiquer : désactivation des évènements alors qu'on n'a pas de proc. d'évènements sur la feuille concernée, ou réinitialiser une variable locale juste avant End Sub qui disparaîtra avec son emplacement mémoire au End Sub, ou With suivi d'une répétition de l'objet concerné à l'intérieur du bloc... Détails par rapport aux points clés...

Cordialement.

Bonjour Klein89,

Merci pour ta réponse.

Si dans la feuille plan de prévention, en D3 tu sélectionnes la valeur 45. Une seule dans les feuilles U..

Ca fonctionne, pourquoi selon toi ?

S'il faut faire une boucle sur les 15 colonnes ça sera beaucoup trop long comme traitement.

J'ai des exemples par ailleurs dont un trouvé sur le net qui a la même structure et ça fonctionne.

Il serait intéressant d'avoir d'autres avis.

Exemple :

Cordialement

Bonjour MFERRAND

Merci pour tes remarques.

Tu sais j'ai testé tellement de choses et comme je ne suis pas à l'origine du fichier natif, je découvre aussi certaines choses.

Notamment je me suis aperçu qu'il y avait un bout de code sur certaines feuilles. C'est pourquoi j'ai placé Application.EnableEvents = False et omis par la suite de le supprimer

J'ai viré les codes inutiles de ces feuilles.

Par contre tu m'intéresses quand tu dis:

- affecter chaque ligne de ce tableau, élément par élément...

- affecter le tableau transposé à la fin.

tu veux dire écrire:

T2(1, c) = T(j, 1)

T2(2, c) = T(j, 2)

etc

et à la fin :

Resize(UBound(T2, 2), UBound(T, 1)) = Application.Transpose(T2)

Je vais faire un essai et je reviens vers toi pour te dire.

Cordialement

re ddetp88,

Et comme ça, ça va pas mieux

T2(c) = Application.Index(T, j, 0) 'affecte la ligne

edit : salut MFerrand

klin89

Re, Salut Klin !

Comme tu l'as vu, avec Klin89 nous avons suivi l'un et l'autre le même type de raisonnement...

Ceci étant, ton fichier exemple fonctionne !

Mon conseil se rattacherait au cas 2 de ton fichier exemple... J'ai déjà eu tenté, sans réussir sinon je n'aurais pas eu la même intervention, une affectation de tableau à un élément de tableau pour ensuite affecter le tableau final...

La méthode cas 3 de ton fichier exemple, utilisant un tableau intermédiaire affecte une ligne de ce tableau intermédiaire en utilisant la fonction INDEX (INDEX(aa, ligne) renvoie en effet la ligne de aa, soit un tableau, affecté à un élément de ton tableau de résultat). Ce tableau est donc un tableau à une dimension dont chaque élément contient un tableau (ligne retenu).

A ce stade, j'avais réussi à faire une affectation ligne par ligne de ce tableau mais pas une affectation globale.

Si la double transposition permet cette affectation globale, je suis preneur de la méthode !

Reste à savoir ce qui l'empêche alors de fonctionner chez toi.

je viens de suivre tes conseils

Voici le code mais il reste un souci, j'ai une ligne vide sur la première ligne.

Sub MisàJourPlanActionPrevention()
Dim T As Variant, T2() As Variant
Dim f As Worksheet, Coef As Integer, c%, i%, j%

Set f = Sheets("Plan de prévention")
'efface le contenu de la feuille plan de prévention
f.Range("A6:O1500").ClearContents
'Coefficient d'évaluation du risque
Coef = f.[D3].Value
'boucle sur les feuilles U..
For i = 1 To Sheets.Count
   If (Left(Sheets(i).Name, 1) = "U" Or Left(Sheets(i).Name, 1) = "u") And Sheets(i).[B4] <> "" Then
      With Sheets(i)
      .Unprotect 'déprotection de la feuille
      T = .Range("B4:P" & .Range("B" & Rows.Count).End(xlUp).Row)
         'boucle sur les lignes des tableaux T
         For j = LBound(T, 1) To UBound(T, 1)
            If T(j, 10) >= Coef Then
               c = c + 1
               ReDim Preserve T2(14, c)
               T2(0, c) = T(j, 1)
               T2(1, c) = T(j, 2)
               T2(2, c) = T(j, 3)
               T2(3, c) = T(j, 4)
               T2(4, c) = T(j, 5)
               T2(5, c) = T(j, 6)
               T2(6, c) = T(j, 7)
               T2(7, c) = T(j, 8)
               T2(8, c) = T(j, 9)
               T2(9, c) = T(j, 10)
               T2(10, c) = T(j, 11)
               T2(11, c) = T(j, 12)
               T2(12, c) = T(j, 13)
               T2(13, c) = T(j, 14)
               T2(14, c) = T(j, 15)
            End If
         Next j
      End With
   End If
Next i
'ecriture du tableau dans la feuille plan de prévention
f.Range("A6").Resize(c, UBound(T, 2)) = Application.Transpose(T2)
End Sub

Le fichier :

Merci à vous deux,

Je reprends la version 1 avec la suggestion de klein89.

T2(c) = Application.Index(T, j, 0) 'affecte la ligne

Je viens de tester sur la version grandeur réelle et la rapidité du traitement des 60 feuilles est tout à fait acceptable.

Merci encore et au plaisir.

Cordialement

Si tu as testé la dernière proposition de Klin89, elle fonctionne !

L'argument étant optionnel, cela n'apparaît pas totalement logique mais en omettant la colonne : erreur 13 (qui résulte probablement du fait que seule la première valeur de la ligne serait affectée par Index et non la ligne entière, car la même erreur apparaît sur la MsgBox pour prélever un élément de la ligne), alors que sur le fichier modèle les même formulations fonctionnent...

En tout cas, en précisant 0 pour la colonne on aboutit bien à l'affectation de la ligne par Index, et le reste suit...

Je note la méthode pour la retester dans d'autres contextes...

Cordialement et bonne continuation.

edit : Je n'avais pas rafraîchi et donc pas vu ta dernière intervention...

Bien que clos, je reviens vers toi car je rencontre un problème (que je ne m'explique pas) Il survient sur la feuille u8.

Tout semble identique et pourtant

Le fichier complet avec les deux codes à tester pour me dire ce que tu en penses.

Merci d'avance de ton expertise.

Cordialement.

Zut le fichier est trop volumineux. 1.8 Mo

Sur le fichier réduit à 29 U je rencontre le même problème après avoir inséré les données provenant du fichier natif.

Si tu modifies le nom de la feuille 8 pour qu'elle ne soit pas testée, tout fonctionne.

Si tu le renommes U8 on termine avec une erreur 13 quelque soit la méthode choisie.

Je sèche. Si tu as une lumière

Cordialement

Re,

Là c'est la feuille qui a un problème. J'ignore lequel mais si elle est là, quel que soit son nom, il y a erreur...

Par ailleurs, si tu reste sur l'option base 0 par défaut et que tu incrémentes c dès le départ, ta ligne 0 sera vide, tu introduis donc une ligne vide dans ton tableau et la dernière ne sera pas affectée. Il faut incrémenter c après affectation de la ligne au tableau, c sera toujours égal à UBound(T2, 2)+1 après affectation, tu n'auras plus de ligne vide et tu pourras utiliser c pour dimensionner la plage en ligne.

NB- Si je reste sur l'option 0 (pour éviter toute erreur), quand je dimensionne en ne partant pas de 0, j'utilise :

T(1 To x) ou T(2 To 50) ou T(-10 To 10)... Et il faut savoir que l'affectation des valeurs d'une plage à une variable de type Variant te produit toujours un tableau à 2 dimensions dont l'indice minimal de chacune est 1.

Cordialement.

Merci pour tes conseils.

Je vais donc prendre des gants avec la feuille U8. Je vais la VIRER!

Chez moi, au petit matin, le paysage s'est paré de blanc. Les paysages sont beaux.

Encore merci et au plaisir d'échanger.

Cordialement.

Bonjour à vous deux,

J'ai enfin trouvé pourquoi j'avais un plantage sur la feuille U8.

J'ai une cellule (L40) qui contient plus de 256 caractères.

Ceci dit je n'ai pas trouvé comment solutionner le bug.

Que le format de la cellule soit standard ou texte c'est la même chose.

Les variables étant déclarées en variant, je ne vois pas trop comment faire.

La ligne qui ne digère pas les cellules de plus de 256 caractères est sans surprise celle du range

f.Range("A6").Resize(c, UBound(T, 2)) = Application.Transpose(T2) 'méthode 1

J'ai solutionné en limitant à 255 caractères avec la fonction left ce qui donne :

T2(11, c) = Left(T(j, 12), 255)

J'ignore si cette information servira. Ne sait-on jamais!

Cordialement

Rechercher des sujets similaires à "range redimensionner tableau"