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

louloubru

XLDnaute Junior
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.


VB:
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

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

louloubru

XLDnaute Junior
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 :

VB:
'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

XLDnaute Barbatruc
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

XLDnaute Junior
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

XLDnaute Barbatruc
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

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

VB:
    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

XLDnaute Barbatruc
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à :
VB:
   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.
 
Dernière édition:

louloubru

XLDnaute Junior
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 ...)
 

Fichiers joints

Dranreb

XLDnaute Barbatruc
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

XLDnaute Junior
Nouveau code :
VB:
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

XLDnaute Barbatruc
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

XLDnaute Junior
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

XLDnaute Barbatruc
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 :
VB:
  ActiveSheet.[A1].Resize(LS, UBound(Tabs, 2)).Value = TabS
 

louloubru

XLDnaute Junior
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.
 

Dranreb

XLDnaute Barbatruc
En débogage mettez des espions sur les indices.

Attendez, vous avez dit sur la même ligne ???
Un Redim Preserve ne peut changer que la limite de la dernière dimension, donc le nombre de colonnes. Pour le nombre de ligne il faut préciser le même qu’auparavant, ou pour être sûr de ne pas se tromper Redim Preserve TabS(1 To UBound(TabS, 1), 1 To UBound(TabE, 2))
Notez que vous pouvez simplifier ça si vous conaissez le nombre de colonnes. Moi je ne le conaissais pas quand j'ai commencé à écrire ça.
 
Dernière édition:

louloubru

XLDnaute Junior
Ohoh maintenant je retombe sur mémoire insuffisante avec cette ligne ...
If UBound(TabS, 2) < UBound(TabE, 2) Then ReDim Preserve TabS(1 To UBound(TabS, 1), 1 To UBound(TabE, 2))

Pas facile les tableaux !
 

Dranreb

XLDnaute Barbatruc
Il y a peut être des cochonnerie à la fin dans une des feuilles ?
Laissez tomber le Redim Preserve, et mettez le nombre de colonnes qu'il vous faut dans le Redim initial.
 

louloubru

XLDnaute Junior
Le nombre de colonnes dans les différentes feuilles va changer. Je ne peux pas le fixer.
Mes données sous sous format tableau d'excel, ce qui a m'a déjà posé quelques soucis... Ca pourrait peut-être être ça.

Je pense que je vais retourner à la méthode initiale avec mes range et cells en cherchant comment enlever la validation...
Parce que là je me décourage :(
 

Dranreb

XLDnaute Barbatruc
Ah ben alors prenez TabE = Sheets(Noms_feuilles(i)).ListObjects(1).DataBodyRange.Value ce sera plus simple.
 

Discussions similaires


Haut Bas