VBA-XL2003-Pb d'erreur 1004 après avoir défini un nom pour une plage

[oracle7]

Bonsoir à tous,

Pour situer les choses, voici ma démarche :
1 - J'ai une feuille 'TOTO' qui comporte une plage A4:Axxx nommée 'XXX' de façon dynamique sur la colonne A.
2 - Je souhaite mettre à jour ma feuille 'TOTO' avec des données externes.
3 - Donc provisoirement je duplique (en dernière position) ma feuille 'TOTO' que je renomme 'SV_TOTO' au passage.
4 - Je vide ma feuille 'TOTO' de ses données (sauf les lignes d'entête de colonnes).
5 - J'importe mes nouvelles données dans la feuille 'TOTO'
6 - Comme tout s'est bien passé, je supprime la feuille de sauvegarde 'SV_TOTO'
7 - Pour finir, je supprime le nom attribué à ma plage A4:Axxx et je le recrée avec le même nom 'XXX' et avec la même référence dynamique à la plage.

Mon problème survient avec une belle erreur 1004 lorsque je recrée le nom 'XXX'.
Je tourne en rond et ne voit pas où se trouve mon erreur. Aussi j'en appelle à vous pour bien vouloir SVP m'aider à corriger cela.
MERCI d'avance.

Je colle ci-après le code épuré qui me pose problème. :

....
' [COLOR="SeaGreen"]On sauvegarde le catalogue courant en dupliquant l'onglet correspondant[/COLOR]
wsFeuilleCatIPD.Copy after:=wbFichierRLS.Worksheets(Worksheets.Count)
Set wsSV_CatIPD = wbFichierRLS.Worksheets(Worksheets.Count)
wsSV_CatIPD.Name = "SV_CatIPD"
'[COLOR="SeaGreen"] On vide le catalogue courant de son contenu sauf la ligne de titre des colonnes[/COLOR]
wsFeuilleCatIPD.Activate
lDerniereLigneCat = wsFeuilleCatIPD.Range("A1").SpecialCells(xlCellTypeLastCell).Row
wsFeuilleCatIPD.Rows("4:" & lDerniereLigneCat).EntireRow.Delete
.....
Ma procédure d'import de données ...
....
' [COLOR="SeaGreen"]On supprime la feuille de sauvegarde du catalogue IPD[/COLOR]
wsSV_CatIPD.Delete
' [COLOR="SeaGreen"]On rétabli les plages nommées associées à la feuille 'CatIPD'[/COLOR]
wbFichierRLS.Names("CIPD_NoCAT").Delete ' [COLOR="SeaGreen"]On supprime d'abord le nom 'CIPD_NoCAT'[/COLOR]
' [COLOR="SeaGreen"]On recrée la plage nommée 'CIPD_NoCAT'[/COLOR]
[COLOR="Magenta"]wbFichierRLS.Names.Add Name:="CIPD_NoCAT", _
    RefersTo:="=DECALER(CatIPD!$A$4;;;NBVAL(CatIPD!$A:$A);1)"[/COLOR]
....

C'est sur cette dernière ligne (en magenta) que le code "plante" avec l'erreur n°1004.

Cordialement
oracle7

 

[Chris401]

Re : VBA-XL2003-Pb d'erreur 1004 après avoir défini un nom pour une plage

Bonsoir

Normalement, en te servant d'indirect dans le nom défini, tu n'as plus besoin de supprimer le nom puis de le renommer

=DECALER(INDIRECT("CatIPD!$A$4");;;NBVAL(INDIRECT("CatIPD!$A:$A")))

Tu peux également faire un clearcontents à la place d'un delete

wsFeuilleCatIPD.Rows("4:" & lDerniereLigneCat).EntireRow.ClearContents

Cordialement

 

[ROGER2327]

Re : VBA-XL2003-Pb d'erreur 1004 après avoir défini un nom pour une plage

Bonsoir à tous

Essayez ceci :

[COLOR="DarkSlateGray"][B]wbFichierRLS.Names.Add Name:="CIPD_NoCAT", _
    RefersTo:="=OFFSET(CatIPD!R4C1,,,COUNTA(CatIPD!C1),1)"[/B]
[/COLOR]

ROGER2327
#3848

Jeudi 19 Gidouille 137 (Saint Sein, tautologue, SQ)
15 Messidor An CCXVIII
2010-W26-6T21:09:31Z

 

[oracle7]

Re : VBA-XL2003-Pb d'erreur 1004 après avoir défini un nom pour une plage

Bonjour à tous,

@ROGER2327

MERCI de ton aide, ta solution marche nickel, comme d'habitude tes réponses sont toujours aussi pertinentes. Bravo et encore Merci.
Au passage et aux vues de cet exemple, je retiens qu'il faut dans le code VBA pour un nom se référant à une formule EXCEL, qu'il faut traduire cette formule en code VBA. Comme quoi on en apprend tous les jours. Dommage que ce ne soit pas expliciter dans l'aide en ligne.

@Chris401

Merci de ta réponse, je retiens ton astuce de "l'INDIRECT".

Pour ce qui est de faire un "clearcontent" au lieu du "delete" : eh bien si je ne fais pas un "delete", le nom existe toujours ce qui d'une part lève l'erreur 1004 et d'autre part conserve un RefersTO erroné --> "=DECALER(CatIPD!#REF!;;;NBVAL(CatIPD!$A:$A);1)".

A tous les deux MERCI encore de votre aide. Pour moi le sujet est donc résolu.

Cordialement
oracle7