[VBA] Appliquer une formule jusqu'à la dernière ligne

Le 26/12/2018 à 19:29

Bonsoir,

A défaut de savoir faire différemment, je souhaiterai, via un code VBA, appliquer une formule (INDEX + EQUIV) jusqu'à la dernière ligne de la colonne B (pas au-delà car ça engendre des erreurs dans la suite du code et parce que ça calcule des lignes pour rien).

Voici le code initial qui fonctionne mais qui s'applique à toutes les lignes de mon tableau :

Sheets("VNEI (EI)").Range(E3, E110).FormulaR1C1 = _
"=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30))"

Sheets("VNEI (EI)").Range("F3:F110").FormulaR1C1 = _
"=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-4],CSV!R2C34:R110C34,0),29)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-4],CSV!R2C34:R110C34,0),29))"

Sheets("VNEI (EI)").Range("G3:G110").FormulaR1C1 = _
"=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-5],CSV!R2C34:R110C34,0),31)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-5],CSV!R2C34:R110C34,0),31))"

Sheets("VNEI (EI)").Range("H3:H110").FormulaR1C1 = _
"=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-6],CSV!R2C34:R110C34,0),36)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-6],CSV!R2C34:R110C34,0),36))"

Sheets("VNEI (EI)").Range("I3:I110").FormulaR1C1 = _     "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-7],CSV!R2C34:R110C34,0),35)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-7],CSV!R2C34:R110C34,0),35))"

Sheets("VNEI (EI)").Range("J3:J110").FormulaR1C1 = _
"=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-8],CSV!R2C34:R110C34,0),37)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-8],CSV!R2C34:R110C34,0),37))"

Et voici le premier essai, qui ne donne rien de bon. Je ne sais pas trop comment m'y prendre.

Dim u As Long

LR = Cells(Rows.Count, "B").End(xlUp).Row

    For u = LR - 1 To 3 Step -1
    For o = 3 To 110
        If Cells(u + 1, 1) = "" Then
            Range(u, o) = "INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30))"
            Else
            Range(u, o) = "INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30))"
        End If
    Next
    Next

J'essai de tricher avec la fonction If sans y parvenir.

Vous auriez une idée ?

Y a t-il besoin d'un document exemple en pièce jointe ?

Merci de votre attention,

Bonne soirée !

Le 26/12/2018 à 20:07

Salut

Je suis pas sur d’avoir comprendre ta question mais voici une approche :

drn = Cells(Rows.Count, "B").End(xlUp).Row

    For rw = 3 To drn
    For cln = 5 To 10
        If Cells(rw, 1) = "" Then
            Range(rw, cln) = "INDEX(CSV!R2C1:R110C47,MATCH(RC[" & incrm1 & "],CSV!R2C34:R110C34,0)," & incrm2 & "))"
        End If
    Next
    Next

'drn =>  jusqu'à la dernière ligne de la colonne B
'rw => numero de ligne ou commence l'insertion des formules ,tu peux ajuster selon tes besion
'cln => normalelement 10 colonnes sont  ,tu peux ajuster selon tes besion

' incrm1 => des valeur ajouter par incrementations si possible ....
' incrm2 =>
'If Cells(rw, 1) = "" Then => biensur il sagit tjr de la colonne "A"

Y a t-il besoin d'un document exemple en pièce jointe ?

Pas forcement, si une bonne explication est fournit, et sur tout si il s’agit d’une simple boucle. Tu peux le faire pour d autres qui auront besoin peut-être ?!

Le 27/12/2018 à 00:40

Merci de votre réponse !

Voici comment se présente le bout de code à tenant compte de ce que vous proposez :

Dim drn As Long

drn = Cells(Rows.Count, "B").End(xlUp).Row
    For rw = 3 To drn
    For cln = 5 To 10
        If Cells(rw, 2) = "" Then '=> Je dois indiquer 2 dans ce cas, puisqu'il s'agit de la colonne B. 
        Range(rw, cln) = "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30))"
        End If
    Next
    Next

Ce que je veut faire, c'est rechercher le contenu de la cellule B3, puis B4, B5 (feuille2), etc. dans la plage AH:AH de la feuille nommée "CSV" ; pour afficher à chaque fois en cellule E3, E4, E5 (feuille2), la correspondance dans la plage de donnée AD:AD (de la feuille "CSV").

=> INDEX(CSV!R2C1:R110C47,

J'indique la plage de données feuille CSV Ligne 2 colonne 1 à Ligne 110 colonne 47.

MATCH(RC[-3],CSV!R2C34:R110C34,0),30)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30))

Je recherche le contenu de ma cellule E3 (pour les autres colonnes j'indique donc RC[-4] ; RC[-5] ; etc.

0 pour correspondance exacte.

30 pour qu'il m'affiche la correspondance de ce que je recherche, dans la colonne n°30.

IF, c'est pour éviter qu'il m'affiche des 0 ; SI la recherche se fait dans des cellules vides et qu'Excel m'affiche 0, alors je lui demande de ne rien faire, sinon, il applique la formule.

Une fois que ça fonctionnera, je répéterai l'opération pour les colonnes F, G, H, I et J.

Ne sachant pas faire ça en pur codage VBA, j'ai directement inscrit la formule. Je n'ai qu'un problème, la formule s'étend sur l'ensemble de la feuille (jusqu'à la limite que je lui donne), sauf que la limite peut varier en fonction de la base de données initiale, aussi, j'aimerais qu'Excel aille chercher la dernière cellule de la colonne B (la A restant vide).

Pour le moment, ça ne fonctionne pas et m'affiche "La méthode 'Range' de l'objet '_Worksheet' à échoué".

En me surlignant ceci : Range(rw, cln) = "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30))"

Par ailleurs, je n'ai pas compris ces parties : "RC[" & incrm1 & "]" ; " & incrm2 & " .

Bonne soirée !

Le 27/12/2018 à 10:51

En le présentant comme ça, il ne se passe rien. Ça semble fonctionner puisque aucune erreur ne s'affiche. 

Dim drn As Long

drn = Cells(Rows.Count, "B").End(xlUp).Row
    For rw = 3 To drn
        If Cells(rw, 2) = "?" Then
        Range(rw, 2) = "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30))"
        Range(rw, 3) = "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-4],CSV!R2C34:R110C34,0),29)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-4],CSV!R2C34:R110C34,0),29))"
        Range(rw, 4) = "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-5],CSV!R2C34:R110C34,0),31)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-5],CSV!R2C34:R110C34,0),31))"
        Range(rw, 5) = "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-6],CSV!R2C34:R110C34,0),36)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-6],CSV!R2C34:R110C34,0),36))"
        Range(rw, 6) = "=IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-8],CSV!R2C34:R110C34,0),37)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-8],CSV!R2C34:R110C34,0),37))"
        End If
    Next

Sinon, j'ai une solution, elle consiste à modifier la formule de départ en ajoutant "IFERROR" :

    Sheets("VNEI (EI)").Range("E3:E110").FormulaR1C1 = _
        "=IFERROR(IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-3],CSV!R2C34:R110C34,0),30)),"""")"
    Sheets("VNEI (EI)").Range("F3:F110").FormulaR1C1 = _
        "=IFERROR(IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-4],CSV!R2C34:R110C34,0),29)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-4],CSV!R2C34:R110C34,0),29)),"""")"
    Sheets("VNEI (EI)").Range("G3:G110").FormulaR1C1 = _
        "=IFERROR(IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-5],CSV!R2C34:R110C34,0),31)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-5],CSV!R2C34:R110C34,0),31)),"""")"
    Sheets("VNEI (EI)").Range("H3:H110").FormulaR1C1 = _
        "=IFERROR(IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-6],CSV!R2C34:R110C34,0),36)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-6],CSV!R2C34:R110C34,0),36)),"""")"
    Sheets("VNEI (EI)").Range("I3:I110").FormulaR1C1 = _
        "=IFERROR(IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-7],CSV!R2C34:R110C34,0),35)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-7],CSV!R2C34:R110C34,0),35)),"""")"
    Sheets("VNEI (EI)").Range("J3:J110").FormulaR1C1 = _
        "=IFERROR(IF(INDEX(CSV!R2C1:R110C47,MATCH(RC[-8],CSV!R2C34:R110C34,0),37)=0,"""",INDEX(CSV!R2C1:R110C47,MATCH(RC[-8],CSV!R2C34:R110C34,0),37)),"""")"

Ça n'engendre plus d'erreur dans le document.

Le 27/12/2018 à 19:14

Salut Le Drosophile

Je suis désolé mais je ne comprends pas cette architecture, peut être un exemple simplifier sur un fichier exemple peut nous aider,

Le 06/01/2019 à 22:38

En effet, étant vraiment novice, je contourne mes problèmes en appliquant des formules via un code VBA (c'est étrange, mais c'est pour une utilisation par différentes personnes et donc éviter les fausse manips (voire les manips tout court).

Mon document fonctionne très bien et il est rapide pour l'instant.

Lorsque j'aurai terminé de le peaufiner, je vais probablement revenir sur ces codes qui appliquent des formules pour essayer de trouver quelque chose de plus propre.

Je vais obligatoirement être poussé à poser à nouveau quelques question sur le forum !

Le 06/01/2019 à 23:18

Salut Le Drosophile et soyez le bienvenu

Le 07/01/2019 à 09:23

Bonjour,

Peut-être qu'en mettant tes données dans un tableau (Insertion -> tableaux) puis en sélectionnant la première cellule d'une ligne et double clic en bas à droite de celle-ci.

Cela permettra d'étendre la formule jusqu'à la dernière ligne dudit tableau.

Rechercher des sujets similaires à "vba appliquer formule derniere ligne"