Amélioration macro historisation

Bonjour à tous,

J'essaie d'améliorer une macro d'historisation de commentaires (la première que j'ai faite, il n'y a pas si longtemps).

Sub Historisation_Commentaires()

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

'Sélectionner l'onglet vdef de mois précédent
    Sheets(2).Activate

'Selectionner la première cellule non vide de la colonne N, etendre la selection, ouvrir le dernier onglet, selection la première cellule non vide de la colonne A
    Range("i10").Select
    Range(Selection, "R10").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(4).Activate
        If Range("A2").Value = "" Then
           Range("A2").Select
        Else
            Range("A1").End(xlDown).Offset(1, 0).Select
        End If

' Coller la valeur des cellules copiées
    Selection.PasteSpecial Paste:=xlPasteValues
' Coller le format des cellules copiées
    Selection.PasteSpecial Paste:=xlPasteFormats

'Suppression de toutes les lignes de commencant pas par "A00" et vidage du presse-papier
With Sheets("Historique commentaires")
   drl = Worksheets("Historique commentaires").Range("A1048576").End(xlUp).Row 'variable dernière ligne
     'boucle de suppression
         For ligne = drl To 2 Step -1
         If Not .Range("A" & ligne) Like "A00*" Then .Rows(ligne).Delete
        Next ligne
End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Code simple qui me permet de copier les commentaires du mois précédent et de les coller dans une feuille excel, à la suite des historiques des périodes précédentes.

Le Hic, c'est que lorsque je rappelle l'historique (autre macro - voir ci-après), il me rappelle toujours les premiers commentaires, pas les derniers.

Afin de ne pas supprimer les historiques précédents, il faudrait donc modifier la macro de rappelle de l'historique pour qu'il commence sa recherche par la fin, et non par le début.

Je sèche

'Selectionner la cellule N18 et boucle + concatener colonnes FGHL pour obtenir une référence unique
    With Sheets(2)
        drl = .Range("H" & .Rows.Count).End(xlUp).Row 'Dernière ligne non vide en colonne M, utilisée comme référence

        ' boucle de concatenation: remplissage colonnes I à R
        For ligne = 10 To drl
           'N° unique (I)
            .Range("I" & ligne) = .Range("A" & ligne) & .Range("B" & ligne) & .Range("C" & ligne) & .Range("G" & ligne)
           [.....]
        'Commentaires (Q)
            .Range("Q" & ligne).FormulaR1C1 = "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)),"""",IF(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)=0,"""",(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)))))"
            'Commentaires autres (R)
            .Range("R" & ligne).FormulaR1C1 = "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,10,FALSE)),"""",IF(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,10,FALSE)=0,"""",(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,10,FALSE)))))"

        Next ligne

    End With

Merci d'avance.

Bonjour,

Si tu veux commencer par la fin et non par le début, il faut modifier la ligne de démarrage de la boucle en mettant:

For ligne = drl To 10 Step -1

Tu pars ainsi de la dernière ligne à la ligne 10, en précisant que tu décrémentes de 1 à chaque tour de boucle (par défaut la valeur du Step est +1)

bonjour,

Merci pour la réponse.

En fait, l'incrémentation doit être faite dans l'ordre.

C'est plus à ce niveau, qu'il faudrait que la recherchev parte de la fin de l'onglet "Historique commentaires":

'Commentaires (Q)
            .Range("Q" & ligne).FormulaR1C1 = "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)),"""",IF(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)=0,"""",(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)))))"

Merci d'avance

Re,

ah d'accord, j'étais à côté de la plaque alors... je me pose une question, est-ce que tu souhaites garder la formule dans le cellule ou bien tu peux te contenter du résultat et refaire tourner la macro si besoin de mettre la valeur à jour?

Dans le premier cas je peux te faire une macro qui serait un équivalent de tes formules et inscrire uniquement le résultat dans la cellule, dans le second cas, je peux te faire une fonction équivalente à RECHERCHEV mais avec la possibilité de lire de bas en haut (ce que la formule RECHERCHEV ne propose malheureusement pas), sinon, il faudrait que tu inverses la chronologie de ton tableau je pense...

Bonjour,

Merci pour ta réponse.

Désolé j'étais en weekend prolongé dans un endroit où je ne captais pas.

Bref

Les deux solutions me vont, du moment que je puisse la comprendre

Bonjour,

Pas de souci, j'ai pu me replonger dans le sujet

Je te propose donc une superbe fonction qui sera à copier dans un module du classeur où tu voudras l'utiliser:

Chercher(valeur_cherchée, table_matrice, no_col_recherche As Long, no_col_résultat As Long, sens_recherche As Integer)

Je t'ai joins un fichier exemple où j'utilise la fonction:

Merci!

Je regarde ça dans l'après-midi

'Selectionner la cellule N18 et boucle + concatener colonnes FGHL pour obtenir une référence unique
    With Sheets(2)
        drl = .Range("H" & .Rows.Count).End(xlUp).Row 'Dernière ligne non vide en colonne M, utilisée comme référence

        ' boucle de concatenation: remplissage colonnes I à R
        For ligne = 10 To drl
            'N° unique (I)
            .Range("I" & ligne) = .Range("A" & ligne) & .Range("B" & ligne) & .Range("C" & ligne) & .Range("G" & ligne)
            ' Total créances HT (J)
            .Range("J" & ligne).FormulaR1C1 = _
                "=+IF(RC[-3]="""","""",IF(RC[-3]=RC[-2],RC[-3],IF(ISERROR(IF(AND(RC[-2]<>0,(RC[-3]/1.2+RC[-2])/(RC[-3]/1.2)<1%),-RC[-2],RC[-3]/1.2)),0,IF(AND(RC[-2]<>0,(RC[-3]/1.2+RC[-2])/(RC[-3]/1.2)<1%),-RC[-2],RC[-3]/1.2))))"
            'Provisions #491 (K)
            .Range("K" & ligne).FormulaR1C1 = "=IF(RC[-4]="""","""",+RC[-3])"
            'Taux (L)
            .Range("L" & ligne).FormulaR1C1 = "=+IF(RC[-5]="""","""",IF(ISERROR(IF(RC[-1]<>0,-RC[-1]/RC[-2],"""")),0,IF(RC[-1]<>0,-RC[-1]/RC[-2],"""")))"
            'Total HT non provisionné (M)
            .Range("M" & ligne).FormulaR1C1 = "=IF(RC[-6]="""","""",+RC[-3]+RC[-2])"
            'Propositions (N)
            .Range("N" & ligne).FormulaR1C1 = _
                "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,6,FALSE)),"""",IF(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,6,FALSE)=0,"""",(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,6,FALSE)))))"
            'Impact dépréciation (O)
            .Range("O" & ligne).FormulaR1C1 = _
                "=+IF(ISERROR(IF(RC14=""provision 100%"",100%*RC[-2],IF(RC14=""provision 50% à 100%"",100%*RC13,IF(RC14=""provision 50%"",50%*RC13,"""")))),0,IF(RC14=""provision 100%"",100%*RC13,IF(RC14=""provision 50% à 100%"",100%*RC13,IF(RC14=""provision 50%"",50%*RC13,""""))))"
            'Impact passage en pertes (P)
            .Range("P" & ligne).FormulaR1C1 = "=+IF(ISERROR(IF(RC14=""pertes"",RC13,"""")),0,IF(RC14=""pertes"",RC13,""""))"
             'Commentaires (Q)
            .Range("Q" & ligne).FormulaR1C1 = "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)),"""",IF(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)=0,"""",(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,9,FALSE)))))"
            'Commentaires autres (R)
            .Range("R" & ligne).FormulaR1C1 = "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,10,FALSE)),"""",IF(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,10,FALSE)=0,"""",(VLOOKUP(RC9,'Historique commentaires'!R2C1:R1048576C10,10,FALSE)))))"

        Next ligne

    End With

Comme tu le vois, je remplis 9 colonnes par une boucle, dont 6 étant des calculs basiques et 3 des fonctions recherchev.

Est-ce que je peux mettre une boucle, dans une boucle?

Par rapport à ta fonction qui me semble vraiment bien, je n'ai pas compris comment tu définis ta matrice, ta colonne de référence et ta colonne de recherche.

en outre, ces éléments, dans mon cas, son dans un autre onglet. Comment je peux adapter la formule?

Merci d'avance.

Bonjour,

Par rapport à ta première question, il est possible de mettre une boucle dans une boucle oui, après ici je ne suis pas sûr que ça ait un énorme intérêt

Pour la formule, je te joins un nouvel exemple et des explications:

Ici j'ai mis le tableau de référence sur "Feuil2", la formule est:

=Chercher(A2;Feuil2!$A$1:$B$5;1;2;1)

Je cherche la valeur de A2, sur la plage de cellules $A$1:$B$5 qui se trouve dans Feuil2 (Feuil2!), ensuite je précise que je cherche ma valeur dans la colonne 1, que je veux le résultat sur la colonne 2, et que je veux chercher de haut en bas (1).

Dans ta formule, tu auras juste à sélectionner le tableau de référence à la place de : Feuil2!$A$1:$B$5, pour le reste il s'agit juste de nombres qui sont indépendant de la feuille, tu peux choisir ton tableau où tu veux, il faudra juste que tu adaptes les numéros en fonction de quelle colonne sert pour la recherche, et laquelle sert pour renvoyer le résultat.

En macro la formule donne:

"=Chercher(RC[-1],Feuil2!R1C1:R5C2,1,2,1)"

J'espère avoir été plus clair

Je m'excuse par avance de la question "con" qui va suivre: il faut les 2 macros pour que cela fonctionne ou si j'insere la fonction "CHERCHER" à la place de mes fonctions RECHERCHEV, ça marche?

Car là ça me renvoi le résultat #NOM (directement inséré dans la cellule) ou " " si inséré dans la macro.

Merci d'avance

Re,

ne t'en fait pas c'est normal comme question, il faut d'abord que tu copies le code de la fonction dans ton classeur sous un module, pour que ton classeur puisse l'appeler, sinon il ne saura pas où aller la chercher et te renverra #NOM

Re-bonjour

J'ai:

• mis la formule (de ton module 2) dans ma macro, résultat: rien.
• copié ton module 1 dans un module 2 sur mon fichier, puis copié la formule d(de ton module 2) dans ma macro, résultat: plantage du fichier.

Il y a donc une étape que je fais mal...

Re-bonjour

C'est très étrange ton plantage... Il y a peut-être quelque chose que tu fais mal oui...

On va procéder par étape, est-ce que quand tu copies ce code :

Function Chercher(valeur_cherchée, table_matrice, no_col_recherche As Long, no_col_résultat As Long, sens_recherche As Integer)
Dim tabRecherche
Application.Volatile

If sens_recherche <> -1 And sens_recherche <> 1 Then Exit Function 'vérification du sens
If no_col_recherche > table_matrice.Columns.Count Or no_col_résultat > table_matrice.Columns.Count Then Exit Function
    'vérification numéros de colonnes
tabRecherche = table_matrice

If sens_recherche = -1 Then 'de bas en haut
    dep = UBound(tabRecherche, 1)
    fin = LBound(tabRecherche, 1)
    pas = -1
ElseIf sens_recherche = 1 Then 'de haut en bas
    dep = LBound(tabRecherche, 1)
    fin = UBound(tabRecherche, 1)
    pas = 1
End If

For i = dep To fin Step pas
    If tabRecherche(i, no_col_recherche) = valeur_cherchée Then
        Chercher = tabRecherche(i, no_col_résultat)
        Exit For
    End If
Next i
End Function

dans un module de ton classeur, tu peux ensuite appeler cette fonction dans tes feuilles en tapant =Chercher()?

Si oui, tu as peut-être un souci avec un paramètre, j'ai regardé si la fonction était compatible avec FormulaR1C1 et elle marche bien

La formule appelée fonctionne dans ma feuille Excel. Et ça marche nickel (Merci!!!)

En fait, je pense que le problème vient du fait que je veuille transformer

ça:

"=+chercher(RC[-5],'Historique commentaires'!R[-54]C[-13]:R[548]C[-4],1,6,-1)"

en ça:

"=+chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,6,-1)"

Il doit y avoir un problème de syntaxe.

Pourtant quand je me mets sur une cellule et que j’exécute la macro, ça marche.

Ah oui en effet, c'est très étrange, surtout si tu me dis que ça marche sur une cellule seule...

ça te met qu'il y a un problème de syntaxe je suppose dans ton histoire... est-ce que c'est le changement du VLOOKUP qui est mal supporté? hmm

Difficile à dire... La fonction marche bien, mais on arrive pas à te l'intégrer dans ta macro pour une raison obscure...

En fait j'ai l'impression que la macro tourne....mais trés trés lentement après modification de celle-ci.

Re, il y aurait peut-être moyen d'optimiser la formule en utilisant un tableau nommé pour éviter de regarder toutes les lignes jusqu'à la 1048576 (ce que fait actuellement la macro), également, tu devrais mettre

Application.Calculation = xlCalculationManual

en début de programme, et

Application.Calculation = xlCalculationAutomatic

en fin de programme, ça évitera que ton classeur recalcule en permanence tout à chaque modification...

Bonjour,

En fait la macro plante suite aux modifications. La ligne surlignée sur le debogage est celle en gras (issue de la macro fournie pour la formule "CHERCHER"):

For i = dep To fin Step pas
[b]If tabRecherche(i, no_col_recherche) = valeur_cherchée Then[/b]
        Chercher = tabRecherche(i, no_col_résultat)
        Exit For
    End If

En fait, a priori il arrive à faire une ligne (si j'appelle la fonction directement dans Excel), mais il n'arrive pas à remplir le tableau.

Merci d'avance

Bonjour,

Merci pour ton retour, c'est vraiment dommage que je ne puisse pas regarder moi même le fichier pour voir où ça plante

Est-ce que ça te renvoie une erreur du type "l'indice n'appartient pas à..." ? Je dois avouer ne pas bien comprendre pourquoi ça plante à cet endroit... ça va prendre un peu de temps pour débuguer, navré...

Ça m’embête aussi de ne pas pouvoir joindre un fichier. Mais il est monté en copiant une extraction d'un logiciel comptable et implémente des colonnes à coté. Je ne peux absolument pas, ni le modifier, ni le mettre sur le forum.

Dans ta fonction, il s'agit d'une :

Erreur Automation

L'objet invoqué s'est déconnecté de ses clients.

Je ne sais pas si ça peut t'aider.

Voici comment j'utilise la fonction CHERCHER dans ma macro (ça peut venir de là aussi):

'Propositions (N)
            .Range("N" & ligne).FormulaR1C1 = _
                "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,6,-1)),"""",IF(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,6,-1)=0,"""",(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,6,-1)))))"
            'Commentaires (Q)
            .Range("Q" & ligne).FormulaR1C1 = "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,9,-1)),"""",IF(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,9,-1)=0,"""",(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,9,-1)))))"
            'Commentaires autres (R)
            .Range("R" & ligne).FormulaR1C1 = "=IF(LEFT(RC9,5)=""Total"","""",IF(ISERROR(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,10,-1)),"""",IF(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,10,-1)=0,"""",(chercher(RC9,'Historique commentaires'!R2C1:R1048576C10,1,10,-1)))))"