Erreur d'exécution '1004

Le 04/04/2019 à 14:41

Bonjour, je suis en train de coder en vba, et je devient fou.

Je réalise un code simple pour faire une plage nommée dynamique, mais impossible ensuite de sélectionner cette plage.

Sub counta()

Sheets("PLANNING").Select
x = Application.WorksheetFunction.counta(Range("D:D")) + 8
y = 3
Z = Cells(65536, 3).End(xlUp).Row - x + 1
ActiveWorkbook.Names.Add Name:="D", RefersToR1C1:=Cells(x, y)
ActiveWorkbook.Names.Add Name:="Nom", RefersToR1C1:="=OFFSET(D,0,0,Z,1)"
Range("Nom").select                                '1
'Sheets("PLANNING").Range("Nom").Select            '2

End Sub

Mon Z est censée prendre la valeur 7 et chose unique mais rageante, c'est que quand je remplace le Z par 7, le code marche.

L'erreur apparaît sur la dernière ligne, que ce soit la ligne 1 ou 2 qui est active. Sauf quand je met 7 évidemment.

Si un bon samaritain pouvait m'aider ça m’éviterait de manger mon clavier.

Merci d'avance.

Le 04/04/2019 à 15:22

Slt hoooodini et bienvenue sur le Forum,

renomme ta plage "Nom" en "Test" par exemple et essaie.

Le 04/04/2019 à 15:31

Merci de l’intérêt que vous portez a mon problème.

Mais non cela ne fonctionne pas.

Le 04/04/2019 à 15:40

Re,

une fois la placge est renommeé en "Test" par exemple, ou bien il faut créer une nouvelle plage, mais le nom "Nom" à éviter^^

aprés il faut absolument sauvegarder le fichier.

Sub counta()

Sheets("PLANNING").Select
x = Application.WorksheetFunction.counta(Range("D:D")) + 8
y = 3
Z = Cells(65536, 3).End(xlUp).Row - x + 1
ActiveWorkbook.Names.Add Name:="D", RefersToR1C1:=Cells(x, y)
ActiveWorkbook.Names.Add Name:="Nom", RefersToR1C1:="=OFFSET(D,0,0,Z,1)"
Range("Test").Select                             '1
'Sheets("PLANNING").Range("Nom").Select         '2

End Sub

sinon change:

Range("Test").Select                             '1

avec

Application.Goto "Test"

ca doit fonctionner

Le 04/04/2019 à 16:30

Ca ne fonctionne toujours pas.

Toujours Erreur d'exécution '1004' mais cette fois petit changement, le message c'est: La référence n'est pas valide.

Ne t'inquiète pas j'avais bien sauvegarder, j'ai même essayer après avoir redémarrer le PC.

Le 04/04/2019 à 16:36

Bonjour,

Je crois que le problème est situé ici :

"=OFFSET(D,0,0,Z,1)"

Dans cette formule, Z reste la lettre Z, puisqu'elle est entre guillemets, Excel l'interprète comme du texte et ne considère pas la valeur contenue dans la variable.

Une piste à tester :

Sub counta()

Dim x As Long, y As Long, z As Long

With Sheets("PLANNING")
    x = Application.CountA(.Range("D:D")) + 8
    y = 3
    z = .Range("C" & .Rows.Count).End(xlUp).Row - x + 1
    ActiveWorkbook.Names.Add Name:="D", RefersToR1C1:=.Cells(x, y)
    ActiveWorkbook.Names.Add Name:="Nom", RefersToR1C1:="=OFFSET(D,0,0," & z & ",1)"
    Range("Nom").select
End With

End Sub

PS : je n'ai pas cherché à comprendre l'objectif réel de toutes ces manipulations, par ce que créer 2 plages nommées pour juste faire une sélection sur la seconde, ça parait un peu... compliqué pour pas grands choses !

Le 05/04/2019 à 08:25

C'est exact, je savais que l'erreur était ici (j'avais tester en remplaçant z par le chiffre actuel 7 et cela fonctionnait).

La modification fonctionne, mais je ne comprend pas pourquoi, normalement c'est toute la formule

 "=OFFSET(D,0,0," & Z & ",1)"

qui est entre guillemet. Je l'utilise ailleurs

"=OFFSET(Feuil1!R3C4,0,0,COUNTA(Feuil1!C4),1)"

mais je ne vois pas de différence, counta et z renvoie des chiffres.

Pour ce qui est du but, c'est pour copier les valeur de la plage Nom qui peut recevoir des lignes supplémentaire, sauf que cette plage est derrière un tableau qui peut aussi voir son nombre de ligne changer.

Mon premier range D sers donc a définir la première cellule a prendre, et je l'utilise uniquement parce que je n'ai pas trouver comment faire autrement.

Le 05/04/2019 à 08:47
hoooodini a écrit :

Pour ce qui est du but, c'est pour copier les valeur de la plage Nom qui peut recevoir des lignes supplémentaire, sauf que cette plage est derrière un tableau qui peut aussi voir son nombre de ligne changer.

Sans fichier, j'ai du mal à voir à quoi ça correspond.

hoooodini a écrit :

La modification fonctionne, mais je ne comprend pas pourquoi, normalement c'est toute la formule

 "=OFFSET(D,0,0," & Z & ",1)"

qui est entre guillemet. Je l'utilise ailleurs

"=OFFSET(Feuil1!R3C4,0,0,COUNTA(Feuil1!C4),1)"

mais je ne vois pas de différence, counta et z renvoie des chiffres.

Il ne faut pas confondre une variable, utilisée pour stocker une valeur, et un texte qui a la même syntaxe qu'une variable. J'ai ici une petite macro exemple, pour voir l'effet de la présence de guillemets :

Sub ComprendreGuillemets()
Nom = "Pedro"
i = 22
MsgBox Nom & i 'Renvoie "Pedro22"
MsgBox "Nom" & i 'Renvoie "Nom22"
MsgBox "Nom & i" 'Renvoie "Nom & i"
End Sub

Quand tu écris une fonction Excel dans une cellule via VBA, tu ne fais qu'écrire du texte selon la bonne syntaxe. Excel interprète ensuite ce texte comme une formule dont il évalue le résultat. Si dans ton texte, tu écris "Z", tu retrouveras un "Z" dans la formule, là où c'est un nombre qui est attendu. En revanche, si tu ne mets pas ton "Z" entre guillemets, VBA placera bien la valeur contenue dans la variable Z.

Le 05/04/2019 à 13:54

D'accord je comprend, au depart c'était surtout que je ne comprenais pas pourquoi mon D a gauche marchait et que la je devait mettre " & z & ".

 "=OFFSET(D,0,0," & Z & ",1)"

Mais si on suis la même logique c'est parce que la la fonction attend un range et que D à été définit en tant que range et que c'est bien un range.

Après pour ce qui est du but, c'est juste pour sélectionner une plage placé après un tableau, mais j'ai réussi a faire ça donc je ne vais pas te prendre ton temps la dessus.

Le 05/04/2019 à 14:04
hoooodini a écrit :

D'accord je comprend, au depart c'était surtout que je ne comprenais pas pourquoi mon D a gauche marchait et que la je devait mettre " & z & ".

 "=OFFSET(D,0,0," & Z & ",1)"

Mais si on suis la même logique c'est parce que la la fonction attend un range et que D à été définit en tant que range et que c'est bien un range.

"D" est un nom de plage valide et utilisable directement dans Excel, donc pas de soucis en l'écrivant ainsi. "Z" est une variable VBA qui n'est disponible que lors de l'exécution de la macro, au sein de celle-ci. Donc dans une cellule Excel, ça ne correspond à rien de connu !

hoooodini a écrit :

Après pour ce qui est du but, c'est juste pour sélectionner une plage placé après un tableau, mais j'ai réussi a faire ça donc je ne vais pas te prendre ton temps la dessus.

Si tu fournis un fichier type, je pense qu'il y a moyen de faire beaucoup plus simple !

PS :

Le 05/04/2019 à 15:39
2forum.xlsx (11.47 Ko)

Voici un fichier exemple, normalement il y a plus de 2000 lignes verte, elles représentent des projets et donc on peut en rajouter ( pas en enlever).

Au niveau du tableau jaune, ça représente les acteurs des dit projets. Je cherche ensuite a créer une page par acteurs pour filtrer les projets et nommer ces pages par les initiales des prénoms.

J'ai déjà toutes les fonctions comme tu peut le voir dans le code. toute variable sont déjà déclarée auparavant

x = Application.counta(Range("D:D")) + 9
y = 3
z = Cells(65536, 3).End(xlUp).Row - x + 1
ActiveWorkbook.Names.Add Name:="D", RefersToR1C1:=Cells(x, y)
ActiveWorkbook.Names.Add Name:="depart7", RefersToR1C1:="=OFFSET(D,0,0," & z & ",1)"

Extraction plage jaune

Après un premier filtrage, les donnée que je doit traiter sont a peut prés 200 lignes

Do                        'les case jaune (depart7 code précédent) sont stocké G1-2 etc
    i = i + 1                    'et leurs initiale (Majuscule) stocké en H1-2 etc
    a = Cells(i, 7).Value
    Cells(i, 7).Activate
    If IsEmpty(ActiveCell) Then
        Exit Do
    End If
    For x = 1 To Len(a)
        Select Case Asc(Mid(a, x, 1))
            Case 65 To 90
                Cells(i, 8).Value = Cells(i, 8).Value + Mid(a, x, 1)
        End Select
    Next
Loop

i = 0
ActiveWorkbook.Names.Add Name:="depart", RefersToR1C1:="=OFFSET(tempo!R1C1,0,0,COUNTA(tempo!C1),5)"
Do
    i = i + 1
    Sheets("tempo").Activate            ' a chaque boucle une page "charge (initiale)" est crée
    a = Cells(i, 8).Value            'dedans sont stockée les 5 premières colonne
    Cells(i, 8).Activate            'et ensuite on trie les lignes avec les Nom. Prénom du tableau jaune
    If IsEmpty(ActiveCell) Then
        Exit Do
    End If
    Sheets.Add(after:=Worksheets("Feuil1")).Name = "charge " & a
    With ActiveWorkbook.Sheets("charge " & a).Tab
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.34
    End With
    b = "arrivee" & a
    ActiveWorkbook.Names.Add Name:=b, RefersToR1C1:="=OFFSET(R1C1,0,0,COUNTA(tempo!C1),5)"
    Sheets("charge " & a).Range(b).Value = Sheets("tempo").Range("depart").Value
    Sheets("tempo").Activate
    Cells(i, 7).Activate
    c = Sheets("tempo").Cells(i, 7).Value
    u = 1
    Sheets("charge " & a).Activate
    Do
        u = u + 1
        d = Cells(u, 5).Value
        Cells(u, 5).Activate
        If IsEmpty(ActiveCell) Then
            Exit Do
        End If
        If d <> c Then
            Rows(u).Delete Shift:=xlUp
            u = u - 1
        End If
    Loop
    Columns("E:E").Delete
Loop

Donc voici les codes que j'utilise, je cherche pas vraiment une correction mais si tu en a une je suis preneur

Mais sinon te prend pas la tête avec ça, mon code fait ce que je lui demande, je chercherait plus tard a l'optimiser.

Le 05/04/2019 à 16:02

Je vais pas avoir le temps d'y jeter un oeil avant la semaine de toute manière, mais j'essayerai de le faire quand même !

Ta problématique initiale étant résolu, pense à valider le sujet.

Rechercher des sujets similaires à "erreur execution 1004"