XL 2013 Erreur définie par l'application ou par l'objet

[louloubru]

Bonjour,

j'ai cette erreur lors de l’exécution de ma procédure, je n'arrive pas à en trouver l'origine car j'utilise la ligne à l'origine de l'erreur dans une autre procédure sans soucis ...

Je vous mets le code ci-dessous, l'objectif est de copier dans un autre classeur toutes les lignes de mon classeur (comportant plusieurs feuilles) répondant à une condition : 1 doit être présent dans ne colonne de la ligne. Cette colonne est choisie par l'utilisateur via un user_form.

Private Sub OK_liste_Click()
'Créé un nouveau classeur contenant les participants à l'évènement sélectionné

    'Trouver la colonne correspondant à l'évènement
    Sheets("Evènements").Activate
    Dim ev As String
    Dim Col As Long
    ev = Me.Evenement.Text
    Col = Range(RefCell(ev)).Column
    
    'Récupérer le nom du classeur de base (Date Grande liste RMT)
    Dim Base
    Base = ThisWorkbook.Name
    
    'Copier coller les lignes des participants concernés par l'évènement : donc les lignes où la case de la colonne Col contient 1
    'Initialisation des variables
    Dim Lig As Long
    Dim NbrLig As Long
    Dim NumLig As Long
    Dim i As Integer
    Dim Celldep As String
    NumLig = 0
    Celldep = "A" & NumLig
    InitialiseNoms_feuilles 'Créé un nouveau classeur
    Workbooks.Add 'Créé un nouveau classeur dans lequel seront copéis les lignes correspondant aux participants de l'évènement.
    ActiveWorkbook.Sheets("Feuil1").Activate ' feuille de destination

    For i = 0 To 13
        With Workbooks(Base).Sheets(Noms_feuilles(i)) ' feuille source
        NbrLig = .Cells(65536, Col).End(xlUp).Row
            For Lig = 1 To NbrLig
                If .Cells(Lig, Col).Value = 1 Then
                [COLOR=rgb(184, 49, 47)][U].Rows(.Cells(Lig, Col).Row).Copy Cells(NumLig, 1)[/U][/COLOR]
                NumLig = NumLig + 1
                End If
            Next
        End With
    Next i

'Uniformiser la mise en forme des cellules (sans bordure Arial 10)
With Sheets("Liste_Bulletin_veille").Range(Celldep)
    .Borders.Value = 0
    .Font.Size = 10
    .Font.Name = "Arial"
    .WrapText = False
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
End With

  Call Unload(Me)

End Sub

Remarque : l'erreur n'est pas due à ma colonne : la fonction RefCell renvoyant l'adresse de la cellule fonctionne (code ci-dessous). Et surtout lorsque j’exécute la procédure en remplaçant col par une colonne existante ("V" par exemple), cela ne fonctionne pas non plus.

J'ai souligné la ligne qui pose problème en rouge.

En vous remerciant !

 

[Dranreb]

Bonjour.
Vous devriez ajouter 1 à NumLig avant de l'utiliser. Parce que Cells(0, 1), ça ne va pas !

 

[louloubru]

Bonjour.
Vous devriez ajouter 1 à NumLig avant de l'utiliser. Parce que Cells(0, 1), ça ne va pas !

Merci !!!
C'était tellement bête ...
Cela fonctionne pas j'ai un autre soucis :
ma fonction copie-colle tout, et non seulement les valeurs.
J'avais traité ce problème lors d'un copier-coller de feuilles en uniformisant la mise en forme à la fin :

'Uniformiser la mise en forme des cellules (sans bordure Arial 10)
With Sheets("Feuil1").Range(Celldep)
    .Borders.Value = 0
    .Font.Size = 10
    .Font.Name = "Arial"
    .WrapText = False
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
End With

Le problème c'est qu'en copiant dans un autre classeur, il ne reconnait plus mes zones de validations de données ...
cela fonctionne mais j'ai une fenêtre qui s'ouvre à chaque fois pour me demander si je veux bien utiliser cette plage ou si je veux la renommer ... Ce qui n'est pas viable

Une autre solution pour copier mes lignes ?

Merci !

 

[Dranreb]

Ne préparez que les valeurs dans un tableau, que vous verserez en une fois tout à la fin.
Partez aussi, en entrée, d'un tableau des valeurs de toute la UsedRange.
Tout cela en sera considérablement plus rapide.

 

[louloubru]

Ne préparez que les valeurs dans un tableau, que vous verserez en une fois tout à la fin.
Partez aussi, en entrée, d'un tableau des valeurs de toute la UsedRange.
Tout cela en sera considérablement plus rapide.

Re-bonjour,
merci beaucoup mais je suis bien incapable de stocker mes lignes dans un tableau ... Je ne sais pas non plus ce que signifie de partir d'un tableau des valeurs de la UsedRange.

 

[Dranreb]

La propriété Value d'un Range de plusieurs cellules est un tableau 2D basé 1.
Donc Dim TDon(): TDon = Worksheets(x).UsedRange.Value vous le fait.

 

[louloubru]

On va y arriver ^^
Alors si j'ai bien compris le principe, cela devrait ressembler à quelque chose comme ça :

    Dim Tableau()
    Tableau = Worksheets(???).UsedRange.Value
    
    For i = 0 To 13
        With ThisWorkbook.Sheets(Noms_feuilles(i)) ' feuille source
        NbrLig = .Cells(65536, Col).End(xlUp).Row
            For Lig = 1 To NbrLig
                If .Cells(Lig, Col).Value = 1 Then
                Tableau(NumLig) = .Cells(Lig, Col).Row
                NumLig = NumLig + 1
                End If
            Next
        End With
    Next i
    
    Workbooks.Add
    ActiveWorkbook.Sheets("Feuil1").Cells.Value = Tableau

J'initialise un tableau au début, je le remplis avec les lignes que je souhaite et ensuite je l'affiche.

Sauf que ...

- Initialisation :

Je ne vois pas comment utiliser UsedRange. J'ai trouvé sur cette fonction : "Renvoie un objet Range qui représente la plage utilisée dans la feuille de calcul spécifiée."

Mais quelle feuille dois-je alors renseigner ? Dans cette version je n'ai pas encore créer le nouveau classeur puisque je n'en ai besoin qu'à la fin. Dois-je le créer avant pour initialiser le tableau ?

- Ajout des lignes dans le tableau :
comme ça : Tableau(NumLig) = .Cells(Lig, Col).Row ? Je voudrais pouvoir lui dire toutes les cellules d'une ligne ... Plutôt comme ça : Tableau (NumLig, ) ?

- Et coller les valeurs de mon tableau dans la feuille à la fin :
J'en reviens au problème du coller ...
Si je lui dit ça : ActiveWorkbook.Sheets("Feuil1").Cells.Value = Tableau, je ne vais pas avoir les bonnes dimensions ...


Merci pour votre aide, c'est un peu compliqué pour moi ...

 

[Dranreb]

Faut arrêter d'utiliser .Cells et .Range à tout bout de champ. C'est horiblement lent ! Des dizaines de milliers de fois plus lent que d'accéder à une variable ou a un élément de tableau ! Ç'est à peu près aussi lent pour une seule cellule que pour des dizaine de milliers mais d'un seul coup.
C'est dans ce genre là :

   Dim Wsh As Worksheet, Col&, TabE(), LE&, TabS(), LS&, C&
   ReDim TabS(1 To 50000, 1 To 10)
   Col = 1
   For Each Wsh In ThisWorkbook.Worksheets
      TabE = Wsh.UsedRange.Value
      If UBound(TabS, 2) < UBound(TabE, 2) Then ReDim Preserve TabS(1 To 50000, 1 To UBound(TabE, 2))
      For LE = 1 To UBound(TabE, 1)
         If TabE(LE, Col) = 1 Then
            LS = LS + 1: For C = 1 To UBound(TabE, 2): TabS(LS, C) = TabE(LE, C): Next C: End If: Next Wsh
   Workbooks.Add
   ActiveSheet.[A1].Resize(LS, CS).Value = TabS

Conseil: ne notez jamais des noms d'objets dans des variables ou tableaux, notez y les objets eux mêmes.
C'est une partie de code qui n'a pas été indiqué, alors je n'en est pas tenu compte, mais au lieu de Nom_feuilles() As String utilisez un TWsh() As Worksheet et faites des Set dedans.

 

[louloubru]

Re
si c'est utilisé à tout bout de champs c'est peut-être parce que c'est plus intuitif ... je suis débutante et j'y arrivais presque.

Je suis désolée mais c'est du chinois pour moi je suis incapable d'appliquer ton code à ma situation où j'ai besoin de copier des lignes de mon classeur qui se trouvent dans plusieurs feuilles dans un nouveau classeur que je crée.

Dans ton code je n'arrive pas à identifier de quelle feuille on parle, je ne vois pas de boucle pour aller chercher les valeurs dans toutes les feuilles ..
Bref. ah et je ne comprends pas ça non plus :
Dim Wsh As Worksheet, Col&, TabE(), LE&, TabS(), LS&, C& : tu créés une nouvelle feuille ? Pourquoi ?


Mon code est au-dessus avec mes interrogations ... Noms_feuilles étant un vecteur contenant le nom des différentes feuilles dans lesquelles aller chercher les lignes.

Je joins mon classeur si besoin (avec toutes les données enlevées ...). Le code initial, avec des cells et des range est associé à la feuille (VBA) Liste_part_evènement. (oui c'est surement très mal fait ...)

 

[Dranreb]

Le fait de déclarer une variable objet ne crée pas d'objet. Pour pouvoir utiliser Wsh il faut soit faire un Set Wsh = une expression Worksheet soit faire un For Each Wsh In une collection d'objets Worksheet.
Dans les déclarations '()' derrière un nom de variable déclare un tableau dynamique, et le '&' signifie simplement ' As Long'.

 

[louloubru]

Nouveau code :

Private Sub OK_liste_Click()
'Créé un nouveau classeur contenant les participants à l'évènement sélectionné

    'Trouver la colonne correspondant à l'évènement
    Sheets("Evènements").Activate
    Dim ev As String
    Dim Col As Long
    ev = Me.Evenement.Text
    Col = Range(RefCell(ev)).Column
    InitialiseNoms_feuilles 'Initialise un vecteur avec le noms des feuilles dans lesquelles je veux chercher mes lignes
    
    'Création d'un tableau contenant mes lignes
    Dim Wsh As Worksheet, TabE(), LE&, TabS(), LS&, C&, i&
    ReDim TabS(1 To 50000, 1 To 10)
    For i = LBound(Noms_feuilles) To UBound(Noms_feuilles)
        TabE = Sheets(Noms_feuilles(i)).UsedRange.Value
        If UBound(TabS, 2) < UBound(TabE, 2) Then ReDim Preserve TabS(1 To 50000, 1 To UBound(TabE, 2))
            For LE = 1 To UBound(TabE, 1)
                If TabE(LE, Col) = 1 Then
                    LS = LS + 1
                        For C = 1 To UBound(TabE, 2)
                        TabS(LS, C) = TabE(LE, C)
                        Next C
                End If
            Next LE
    Next i
  
   'Création d'un nouveau classeur et "collage" du tableau contenat les lignes dans ce classeur
   Workbooks.Add
   ActiveSheet.[A1].Resize(LS, C).Value = TabS
  
  Call Unload(Me)

End Sub

Je ne comprends pas ton code mais j'essaie.
j'ai remis ma boucle for et enlevé le For each Worksheet car je ne veux pas parcourir toutes mes feuilles mes seulement certaines d'entre-elles.

J'obtiens une erreur "mémoire insuffisante"
Cette ligne est surlignée en débogage : If UBound(TabS, 2) < UBound(TabE, 2) Then ReDim Preserve TabS(1 To 50000, 1 To UBound(TabE, 2))

Jamais eu cette erreur encore ...

 

[Dranreb]

Réduisez 50000 au nombre maximum de lignes risquant d'être produites. C'est sûr que s'il y a pas loin d'une centaine de colonnes ça fait beaucoup …

 

[louloubru]

J'ai réduit à 1000 et ai une nouvelle erreur sur la même ligne : l'indice n'appartient pas à la sélection ... Pourtant les variables sont définies !

 

[Dranreb]

Alors c'est qu'il faut plus de 1000 lignes.

Indépendamment de cela, j'ai commis une erreur sur ma dernière instruction. Il Faut :

  ActiveSheet.[A1].Resize(LS, UBound(Tabs, 2)).Value = TabS

 

[louloubru]

Je suis remontée à 5000 (plus d'erreur de mémoire aller savoir pourquoi ...) avec toujours ce soucis d'indice n'appartenant pas à la sélection ...
Je n'est pas plus de 5000 lignes c'est certain donc ce doit être lié à autre chose.