Ecriture d'une formule dans une cellule avec erreur 1004

Bonjour,

Pas encore très rodé au vba, je suis confronté à l'erreur 1004 (Méthode Range de l'objet Global à échoué) dans le contexte suivant:

Je cherche à écrire une formule dans une cellule dans une cellule, avec des références de ligne et colonnes calculées, qui bloque à l'exécution de la macro.

Mon code:

Sub AjoutSitu()

'Recherche de la dernière colonne de la ligne des n° de situ utilisée
    Dim DerCol As Integer
    DerCol = Cells(4, Columns.Count).End(xlToLeft).Column

'Récup de le valeur de la cellule
    Dim a As String
    a = Cells(4, DerCol).Value

'Extraction n° situ pour n° nvelle situ
    Dim b As Integer
    b = Right(a, 2)

'Recherche de la dernière ligne de la colonne Dercol
    Dim DerLigne As Integer
    DerLigne = Range("H" & Rows.Count).End(xlUp).Row

'Copier/coller de la nvelle Situ
    Range(Cells(4, DerCol), Cells(DerLigne, DerCol + 3)).Select
    Selection.Copy
    Cells(4, DerCol + 4).Select
    ActiveSheet.Paste
    'sortie de la selection de cellules précédente
    Cells(1, 1).Select

'Renommage nvelle situ
    Dim DerColN As Integer
    DerColN = Cells(4, Columns.Count).End(xlToLeft).Column
    'nommage
    Cells(4, DerColN).Value = "SITU " & (b + 1)

'Vidage cellules de la colonne "Réalisé %"
    Range(Cells(9, DerColN), Cells(DerLigne - 9, DerColN)).ClearContents
    Range(Cells(9, DerColN + 2), Cells(DerLigne - 9, DerColN + 2)).ClearContents

'Renommages totaux avec n° nvelle situ
    Cells(DerLigne - 7, DerCol + 4).Value = "TOTAL S" & (b + 1) & " HT"
    Cells(DerLigne - 5, DerCol + 4).Value = "TOTAL S" & (b + 1) & " TTC"
    Cells(DerLigne - 7, DerCol + 6).Value = "TOTAL S" & (b + 1) & " HT"
    Cells(DerLigne - 5, DerCol + 6).Value = "TOTAL S" & (b + 1) & " TTC"

'Dde date situ
    Dim DateSitu As String
    DateSitu = InputBox("Date de la situation :", "DATE DE LA NOUVELLE SITUATION", "jj/mm/aa ")
    Cells(5, DerColN).Value = DateSitu

'Si autoliquidation

    If MsgBox("Situation avec Autoliquidation?", vbQuestion + vbYesNo) = vbYes Then
        'Modif TVA Titulaire
        Range(Cells(DerLigne - 6, DerCol + 5)).Formula = "= (Cells(DerLigne - 7, DerCol + 5) + Cells(DerLigne - 7, DerCol + 7)) * 0.2"

        'Modif nom TVA SST
        Cells(DerLigne - 5, DerCol + 6).Value = "Autoliquidation"
        Cells(DerLigne - 5, DerCol + 6).HorizontalAlignment = xlCenter

        'Modif nom TTC SST
        Cells(DerLigne - 6, DerCol + 6).Value = "Autoliquidation"
        Cells(DerLigne - 6, DerCol + 6).HorizontalAlignment = xlCenter

        'Modif montant TVA SST
        Cells(DerLigne - 6, DerCol + 7).Value = "/"
        Cells(DerLigne - 6, DerCol + 7).HorizontalAlignment = xlCenter

        'Modif montant TTC SST
        Cells(DerLigne - 5, DerCol + 7).Value = "/"
        Cells(DerLigne - 5, DerCol + 7).HorizontalAlignment = xlCenter

        'Modif montant TVA Situ
        Cells(DerLigne - 3, DerCol + 4).Value = Cells(DerLigne - 6, DerCol + 5).Value

        Else: Cancel = True

    End If
'Redimensionnement des colonnes de la nouvelle situ
    Columns(DerCol + 4).EntireColumn.AutoFit
    Columns(DerCol + 6).EntireColumn.AutoFit

End Sub

la ligne en jaune au débogage est:

Range(Cells(DerLigne - 6, DerCol + 5)).Formula = "= (Cells(DerLigne - 7, DerCol + 5) + Cells(DerLigne - 7, DerCol + 7)) * 0.2"

Quelqu'un saurait m'aider?

Hello,

Range(Cells(DerLigne - 6, DerCol + 5)).Formula =

Attends une syntaxe du style A1 :

Range(Cells(DerLigne - 6, DerCol + 5)).Formula = "=$A$4+$A$10"

Donc soit tu transformes ton

Cells(DerLigne - 7, DerCol + 5) + Cells(DerLigne - 7, DerCol + 7)) * 0.2

en style A1

ou bien tu fais ceci

Range(Cells(DerLigne -6, Dercol +5)) = Cells(DerLigne - 7, DerCol + 5) + Cells(DerLigne - 7, DerCol + 7)) * 0.2

Bonjour Rag02700,

Merci pour cette réponse.

J'ai du oublier de préciser que c'est une formule qu'il me faut écrire dans une cellule par le biais de cette ligne, pour qu'à l'utilisation le calcul se fasse automatiquement.

pour ta 2ème solution, c'est un calcul donc une valeur qui ne se modifiera pas à l'utilisation.

Pour la 1ère solution, s'agissant de références de cellules à déterminer, je ne peux transformer mon:

Cells(DerLigne - 7, DerCol + 5) + Cells(DerLigne - 7, DerCol + 7)) * 0.2

Salut,

Si tu as le numéro de ligne et le numéro de colonne, tu peux parfaitement trouver l'adresse de la cellule.

Je t'invite à aller voir ce topic : https://forum.excel-pratique.com/excel/recherche-de-la-lettre-d-une-colonne-112889 afin d'identifier la lettre de colonne correspondant à ton numéro, ainsi tu pourras rentrer une formule avec le bon format.

Bibu

Bonjour,

Une piste, en complément de ce qui a déjà été dit :

Range(Cells(DerLigne - 6, DerCol + 5)).Formula = "=" & Cells(DerLigne - 7, DerCol + 5).Address & "+" & Cells(DerLigne - 7, DerCol + 7)).Address & "*0.2"

Attention, si la formule diffère à chaque ligne, il faudra utiliser une boucle ou l'écrire d'une manière différente (style R1C1 par exemple).

Quelques remarques concernant le code initial :
- La déclaration de toutes les variables est à grouper en début de macro (à éviter en cours de route)
- Le type d'une variable doit concorder avec ce qui lui est affecté, par exemple b = Right(a, 2) --> la fonction Right renvoie un texte (même s'il est constitué de chiffres). On peut utiliser la fonction CInt() pour s'assurer de la conversion
- Les instructions .Select ou .Activate sont inutiles si les objets manipulés sont bien précisés

Bonjour Bibu,

Justement, c'est la tout le pb, je ne connais pas à l'avance les n° de ligne et de colonne, sinon j'aurais déjà la solution

Merci toute de même pour ton aide

Bonjour Pedro22,

Merci pour ton cette réponse, avec plein de bonnes choses sur le code initial (notamment la fonction Right)

Pour la ligne en question, l'idée semble intéressante mais renvoie une erreur de syntaxe que je ne retrouve pas...

En complément, la formule ne diffère pas à chaque ligne

ThP a écrit :

Pour la ligne en question, l'idée semble intéressante mais renvoie une erreur de syntaxe que je ne retrouve pas...

Peux tu joindre un fichier de test, que je puisse reproduire et identifier le problème ?

Tu peux aussi faire afficher la formule dans une MsgBox pour contrôler qu'elle ressemble bien à quelque chose de conforme.

Pedro22,

J'ai trouvé l'erreur de syntaxe (une parenthèse de trop), mais du coup j'ai toujours le même message d'erreur 1004 sur cette même ligne.

S'agissant de références de cellules parfaitement connues dans le fichier modèle Excel mais qui seront amenées à évoluer à l'utilisation, je vais tenter de controuver le pb par des cellules nommées ...

Pour le cas ou, voici le fichier de test

En modifiant ta formule de calcul de TVA par celle-ci, tu n'as plus à gérer de cas particulier dans ta macro :

=SI(J139="Autoliquidation";I138+K138;I138)*0,2

J'ai aussi fait un peu de ménage dans ton code, je te laisse tester.

Sub AjoutSitu()

Dim DerCol As Integer, b As Integer, DerLigne As Integer, DerColN As Integer

With Sheets("Suivi Situ")
    DerCol = .Cells(4, Columns.Count).End(xlToLeft).Column 'Recherche de la dernière colonne de la ligne des n° de situ utilisée
    b = CInt(Right(.Cells(4, DerCol), 2)) 'Extraction n° situ pour n° nvelle situ
    DerLigne = .Range("H" & Rows.Count).End(xlUp).Row 'Recherche de la dernière ligne de la colonne Dercol
    .Range(.Cells(4, DerCol), .Cells(DerLigne, DerCol + 3)).Copy .Cells(4, DerCol + 4) 'Copier/coller de la nvelle Situ
    .Cells(4, DerCol + 4).Value = "SITU " & (b + 1) 'nommage
    'Vidage cellules de la colonne "Réalisé %"
    .Range(.Cells(9, DerCol + 4), .Cells(DerLigne - 9, DerCol + 4)).ClearContents
    .Range(.Cells(9, DerCol + 6), .Cells(DerLigne - 9, DerCol + 6)).ClearContents
    'Renommages totaux avec n° nvelle situ
    .Cells(DerLigne - 7, DerCol + 4).Value = "TOTAL S" & (b + 1) & " HT"
    .Cells(DerLigne - 5, DerCol + 4).Value = "TOTAL S" & (b + 1) & " TTC"
    .Cells(DerLigne - 7, DerCol + 6).Value = "TOTAL S" & (b + 1) & " HT"
    .Cells(DerLigne - 5, DerCol + 6).Value = "TOTAL S" & (b + 1) & " TTC"
    'Dde date situ
    .Cells(5, DerCol + 4).Value = InputBox("Date de la situation :", "DATE DE LA NOUVELLE SITUATION", "jj/mm/aaaa ")
    'Si autoliquidation
    If MsgBox("Situation avec Autoliquidation?", vbQuestion + vbYesNo) = vbYes Then
        'Modif nom TVA SST
        .Cells(DerLigne - 5, DerCol + 6).Value = "Autoliquidation"
        .Cells(DerLigne - 5, DerCol + 6).HorizontalAlignment = xlCenter
        'Modif nom TTC SST
        .Cells(DerLigne - 6, DerCol + 6).Value = "Autoliquidation"
        .Cells(DerLigne - 6, DerCol + 6).HorizontalAlignment = xlCenter
        'Modif montant TVA SST
        .Cells(DerLigne - 6, DerCol + 7).Value = "/"
        .Cells(DerLigne - 6, DerCol + 7).HorizontalAlignment = xlCenter
        'Modif montant TTC SST
        .Cells(DerLigne - 5, DerCol + 7).Value = "/"
        .Cells(DerLigne - 5, DerCol + 7).HorizontalAlignment = xlCenter
        'Modif montant TVA Situ
        .Cells(DerLigne - 3, DerCol + 4).Value = Cells(DerLigne - 6, DerCol + 5).Value
    Else: Cancel = True
    End If
    'Redimensionnement des colonnes de la nouvelle situ
    .Columns(DerCol + 4).EntireColumn.AutoFit
    .Columns(DerCol + 6).EntireColumn.AutoFit
End With

End Sub

Un très très grand merci à toi, Pedro22.

je n'en demandais pas autant, mais je prends tout avec Plaisir