La méthode Range de l'objet Worksheet a échoué (438)

Le 31/12/2019 à 14:33

Bonjour à tous,

J'ai une erreur assez bête qui m'empêche l'exécution de mon code.

Je vous explique.

J'ai fait une macro qui permet de calculer le taux de retard en fonction du code gestionnaire comptable (Les clients sont répartis dans des portefeuilles et affectés à un code gestionnaire comptable unique).

Jusque là pas de problèmes. Je récupère la base, fais les calculs nécessaires et édite les graphiques.

Cependant, j'utilise un autre tableau pour affecter les codes gestionnaires comptables en face de chaque client (Pour des raisons évidentes de RGPD, je ne peux pas partager ce type de classeur...)

Voilà le code qui me pose soucis :

Sub Maj_gest_compt()
    Application.Workbooks.Open "MonFichier"
    Set Correspondance = Workbooks("Correspondance gest comptable AA1.xlsx").Sheets("Payeur")
    Set Plage_recherche = Correspondance.Range("A2:C24000")
    With KPI.Sheets("Base de données")
        .Activate
        derniere_ligne = .Cells(Application.Rows.Count, 1).End(xlUp).Row
        For n = 2 To derniere_ligne
            .Cells(n, 8) = Application.WorksheetFunction.VLookup(.Cells(n, 4), Correspondance.Range(Plage_recherche), 3, False)
        Next
        Workbooks("Correspondance gest comptable AA1.xlsx").Save
        Workbooks("Correspondance gest comptable AA1.xlsx").Close
    End With
End Sub

PRECISIONS :

Les objets suivants sont déclarés :

Correspondance as worksheet

Plage_recherche as range

KPI as workbook

n as Long

derniere_ligne as variant

MonFichier désigne le fichier que je vais chercher (emplacement réseau)

La ligne qui me pose soucis est la suivante :

.Cells(n, 8) = Application.WorksheetFunction.VLookup(.Cells(n, 4), Correspondance.Range(Plage_recherche), 3, False)

Et me renvoie l'erreur La méthode 'Range' de l'objet '_Worksheet' a échoué

J'ai vérifié si mon Range était bien défini, vérifié si les données cherchées étaient bien dans le tableau de recherche, rien à faire je ne trouve pas la solution, qui pourrait paraît si simple...

Merci d'avance pour votre coup de main !

MR

Le 31/12/2019 à 14:53

Bonjour,

Votre imbrication successive d'objets est aussi lourde qu'inutile !

Exemple :

 Set Correspondance = Workbooks("Correspondance gest comptable AA1.xlsx").Sheets("Payeur")
 Set Plage_recherche = Correspondance.Range("A2:C24000")
'Devient :
 Set Plage_recherche = Workbooks("Correspondance gest comptable AA1.xlsx").Sheets("Payeur").Range("A2:C24000")
 'Ou :
 With Workbooks("Correspondance gest comptable AA1.xlsx").Sheets("Payeur")
     Set Plage_recherche = .Range("A2:C24000")
     '...
End With

Par ailleurs, votre erreur est liée à cette syntaxe incorrecte :

Correspondance.Range(Plage_recherche)

Il faut écrire seulement Plage_recherche, qui est déjà un objet range rattaché à l'onglet "Payeur" de votre classeur "Correspondance"...

Le 31/12/2019 à 14:55

Bonjour,

Quelques propositions :

1- déclarez vos variables au bon endroit

Les déclarations de variables Public sont à éviter (trop source d'erreurs)...

Sub Maj_gest_compt()
Dim Correspondance As Worksheet
Dim Plage_recherche As Range
Dim n As Long, derniere_ligne As Long

    'bla bla bla

2- Utilisez les vos variables :

en remplaçant :

.Cells(n, 8) = Application.WorksheetFunction.VLookup(.Cells(n, 4), Correspondance.Range(Plage_recherche), 3, False)

par :

.Cells(n, 8) = Application.WorksheetFunction.VLookup(.Cells(n, 4), Plage_recherche, 3, False)

Plage_recherche étant un objet "Range"...

3- Spécifiez clairement la propriété que vous souhaitez utiliser pour chaque objet :

Cells(n, 8) représente un objet. De type "Range". La propriété que vous cherchez à établir est celle par défaut : .Value. Mais, pour Cells(n, 4), (surtout si la colonne 4 contient des formules, des dates, etc...), la propriété est, peut-être, .Text...

Alors :

.Cells(n, 8) = Application.WorksheetFunction.VLookup(.Cells(n, 4), Correspondance.Range(Plage_recherche), 3, False)

deviendrait :

.Cells(n, 8).Value2 = Application.WorksheetFunction.VLookup(.Cells(n, 4).Text, Plage_recherche, 3, False)

4- Je n'en suis pas fan...

Préférez ne pas utiliser WorksheetFunction.

La plupart du temps cela ne sert à rien. Application.formula est moins source d'erreur, en VBA...

.Cells(n, 8).Value2 = Application.VLookup(.Cells(n, 4).Text, Plage_recherche, 3, False)

devrait suffire...

5- Je ne comprends pas l'utilité de votre code...

Mais ça, c'est juste perso...

Ces bonnes habitudes, même si elles ne règlent pas le problème actuel, vous permettront de mieux coder...

EDIT : Salut Pedro !

Le 31/12/2019 à 15:18

Bonjour pijaku et Pedro22,

Merci de prendre de votre temps pour m'aider.

Je suis encore novice en VBA, je n'ai effectivement pas encore les bonnes pratiques et méthodes, vos conseils me sont précieux.

Pour pijaku :

1) J'utilise ces variables dans différentes modules, qui sont organisés par étapes, la déclaration en public m'évite de les redéfinir dans chaque module. Pourquoi cela est source d'erreurs ?

2) Bêtement j'utilise un Range dans un Range... Je n'avais pas saisi la subtilité jusqu'alors ! Merci !

3) Je vais faire en sorte d'y faire plus attention, je vais retravailler mon code en fonction.

4) En général je ne l'utilise pas, mais j'ai tellement cherché que j'ai tout essayé, même des choses bêtes... Mea culpa

5) Ce code me permet de mettre à jour, en fonction du code client, le code gestionnaire associé. Je suis obligé de recouper avec une base externe car les données (du moins, spécifiquement CETTE donnée là) peut s'avérer erronée... Ce fichier étant utilisé dans le cadre de stats et d'analyse pour l'équipe comptable, il faut que la base soit évidemment la plus correcte possible... D'où l'utilité de ce code

Pour Pedro22 :

Merci pour l'aide, en effet, comme dis plus haut à pijaku, je n'avais pas saisi la subtilité du range, je le saurais pour la prochaine fois ! Encore merci !

Le 31/12/2019 à 15:26

J'utilise ces variables dans différentes modules, qui sont organisés par étapes, la déclaration en public m'évite de les redéfinir dans chaque module. Pourquoi cela est source d'erreurs ?

Une variable Public, notamment de Type Worksheet ou Range, est modifiable dans n'importe lequel de vos modules, par une fonction ou une procédure.

De ce fait, votre code devient difficilement lisible, maintenable.

Si vous avez besoin qu'une variable soit "transmise", mieux vaut la passer en paramètre de vos fonctions et/ou procédure.

Un exemple :

Sub Test()
Dim sString As String, iInt As Integer
   sString = "Lettre"
   iInt = Procedure(sString)
End Sub

Private Function Procedure(sStr As String) As Integer
   Select Case sStr
      Case "Lettre"
         Procedure = 1
      Case "Chiffre"
         Procedure = 8
      Case Else
         Procedure = -1
   End Select
End Function
Le 31/12/2019 à 15:37

Supposant que le fichier ouvert au début de votre macro est : "Correspondance gest comptable AA1.xlsx" et qu'il se trouve dans le même répertoire que celui contenant la macro

Une proposition de code "nettoyé" :

Sub Maj_gest_compt()

   Dim Plage_recherche As Range, n As Long

    Workbooks.Open ThisWorkbook.Path & "/Correspondance gest comptable AA1.xlsx" 'Devient le classeur actif
    Set Plage_recherche = Sheets("Payeur").Range("A2:C24000")
    With KPI.Sheets("Base de données")
        For n = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(n, 8) = Application.VLookup(.Cells(n, 4), Plage_recherche, 3, False)
        Next n
        ActiveWorkbook.Close True '"True" pour sauvegarder si modification
    End With
End Sub
Le 31/12/2019 à 16:10

Je vous remercie Pedro22 pour votre proposition de code

Néanmoins, je ne peux l'adapter dans mon cas car, malheureusement, il ne s'agit pas du fichier ouvert au début de la macro, et il n'est pas au même emplacement que le fichier macro... Mais la proposition est tout de même noté, je vais travailler à un code plus propre et donc plus performant !

Rechercher des sujets similaires à "methode range objet worksheet echoue 438"