Fonction recherche avancee possible en VBA ?

FONCTION RECHERCHE AVANCEE :

Objectif de la recherche, visualiser chaque ligne et lorsqu'il y a un montant dans la colonne PAYEMENT (225,00 €), mettre en mémoire le N° PIECE (ACE03/015), le nom de l'ORGANISME (EARL de ROUVAU) puis le PAYEMENT (225,00 €)

puis chercher dans l'onglet "BASE COMPTA FOURNISSEURS" le N° PIECE (ACE03/015), le nom de l'ORGANISME (EARL de ROUVAU) puis le montant au CREDIT (225,00 €), après cette étape, REGARDER dans la colonne Lettrage s'il existe 1 lettre ,

s'il n'y a pas de lettre, donc il n'y a pas de réglement, s'il existe une lettre, alors chercher la même lettre dans la colonne LETTRAGE mais au DEBIT.

puis COPIER la date qui figure dans la colonne DATE PIECE et la COLLER dans l'onglet TABLEAU BORD COLONIES, dans la colonne DATE REGLMT, et enfin copier le numéro qui correspond au numéro du virement qui se situe dans l'onglet BASE COMPTA FOURNISSEURS, dans la colonne Réf.Justificative et coller ce numéro dans l'ONGLET TABLEAU DE BORD COLONIES, colonne REF. VIRT.

Ainsi de suite en balyant chaque ligne de l'ONGLET TABLEAU DE BORD COLONIES.

Je ne sais pas si vba peut faire ça, mais ça serait magnifique de finir ce dossier.

Je vous remercie infiniment pour votre aide.

Bonjour Miloud 57000 le forum

Je pensais que de dire bonjour était un bon début pour un post.

Comme quoi on peut se tromper !!!!!

Bonne journée et bon Weekend

Papou

Re bonjour Miloud 57000 le forum

Je ne sais pas si vba peut faire ça, mais ça serait magnifique de finir ce dossier.

Si c'est possible sans problème, mais c'est pas vba qui va savoir faire, mais le gars qui va faire le programme en vba.

a+

Papou

Désolé Papou et Paritec, oui c'est vrai une grave une trés grave erreur de ma part de ne pas avoir dit bonjour ou bonsoir

Vous êtes rancunier au point de ne pouvoir m'aider ? Simplement parce que

je n'ai pas dit bonjour ou bonsoir ?

C'est pas sympa je trouve surtout lorsqu'on a besoin d'aide pour avancer...

Bonjour Miloud,

Juste un mot pour te dire que je viens tout juste de commencer ton dossier. j'te garantis rien, mais j'vais essayer.

dans ton énoncé initial, « mettre en mémoire » n'est pas clair ! à quel emplacement : quelle feuille ? quelle cellule ?

dhany

Tout d'abord merci pour ton aide, et je sais que c'est pas trop facile ce que je demande.

Lorsque je dis, mettre en mémoire, ça veut dire le copier dans un endroit provisoire pour ensuite le comparer avec ce que je cherche pour enfin copier la date et le numéro du virement.

Je ne sais pas si c'est très clair ce que je viens d'écrire. N'hésites pas à me le dire pour que je reformule plus simplement...

Je te retourne ton fichier modifié :

À l'ouverture du fichier, tu es sur la 1ère feuille "TABLEAU DE BORD COLONIES"

note bien que W6 et X6 sont vides ; Ctrl e ➯ travail effectué

La macro ne se lance que depuis la 1ère feuille : inopérante depuis la 2ème feuille

Alt F11 pour voir la macro, puis revenir sur Excel

Si besoin, tu peux demander une adaptation.

Merci de me dire si ça te convient.

dhany

Bonjour Dhany et sincerement je te remercie infiniment pour ton aide précieuse.

Insomniaque

Malheureusement ça ne fonctionne pas comme je le souhaite, je vais donc te donner une explication plus simple avec moins de détail, je pense que d'avoir détaillé mon besoin n'était pas une bonne chose.

Reprenons en résumant :

ETAPE 1 :

Nous sommes dans l'onglet "TABLEAU DE BORD COLONIES"

Le programme doit chercher le numéro de la pièce qui commence par "ACE" qui figure dans la colonne "F" et il doit aller chercher ce même numéro de pièce dans l'onglet "BASE COMPTA FOURNISSEURS" avec un montant au CREDIT (colonne R).

ETAPE 2 :

Nous sommes dans l'onglet "BASE COMPTA FOURNISSEURS"

S'il trouve ce même n° pièce qui commence par "ACE", il va regarder dans la colonne "P" s'il trouve une lettre (pour information, lettre = de "A" à "Z"...) qui doit être au DEBIT (colonne Q),

Après avoir trouvé cette même lettre d'abord au CREDIT (colonne R) puis au DEBIT (Colonne Q), il doit copier la date qui est dans la colonne "G", date qui est situé avec la ligne du DEBIT (Colonne Q) et la coller dans l'onglet "TABLEAU DE BORD COLONIES" correspondant au n° de la pièce trouvée Colonne W.

et idem pour le N° VIRT, il doit extraire le numéro qui est dans la colonne H et l'inscrire dans l'onglet "TABLEAU DE BORD COLONIES" colonne X.

Pour résumer, il cherche, il trouve, il copie la date et le n° du virt.

J'espère que c'est plus facile à comprendre

Je crois qu'il y a un problème dans le module, il me semble que le programme cherche dans un premier temps le n° de la pièce qui est dans l'onglet "TABLEAU DE BORD COLONIES" dans l'onglet "BASE COMPTA FOURNISSEURS" et ensuite il cherche ce même n° Pièce en fesant une boucle uniquement avec l'onglet "BASE COMPTA FOURNISSEURS", voilà pourquoi, le résultat attendu n'est pas celui que je cherche.

La réponse par exemple pour la pièce ACE03/015, il aurait dû trouver la lettre D qui est au DEBIT et ensuite il fesait une copie de la date, le "27/03/2018" et du n° du virement "12" dans l'onglet TABLEAU DE BORD COLONIES.

Je joins ci-dessous ton module :

Option Explicit

Sub Essai()
  If ActiveSheet.Name <> "TABLEAU DE BORD COLONIES" Then Exit Sub
  Dim bcf As Worksheet, dlig&: Set bcf = Worksheets("BASE COMPTA FOURNISSEURS")
  dlig = bcf.Cells(Rows.Count, 1).End(xlUp).Row: If dlig < 7 Then Exit Sub
  Dim c0 As Range, c1 As Range, pmt@, org$, pce$, ltr$, s$, lg0&, lg1&, n&, dv&
  Set c1 = [G5]: n = Cells(Rows.Count, 2).End(xlUp).Row - 5
  Application.ScreenUpdating = 0
  For dv = 0 To n
    pmt = c1.Offset(dv, 14)
    If pmt > 0 Then
      org = c1.Offset(dv): pce = c1.Offset(dv, -1)
      For lg0 = 7 To dlig
        Set c0 = bcf.Cells(lg0, 10)
        If c0 = pce Then
          If c0.Offset(, 2) = org Then
            If c0.Offset(, 8) = pmt Then
              ltr = c0.Offset(, 6)
              If ltr <> "" Then
                For lg1 = lg0 + 1 To dlig
                  Set c0 = bcf.Cells(lg1, 17)
                  If c0 <> 0 Then
                    If c0.Offset(, -1) = ltr Then
                      c1.Offset(dv, 16) = c0.Offset(, -10)
                      s = Trim$(Replace$(c0.Offset(, -9), "Rglt Viremnt", ""))
                      If s <> "" Then c1.Offset(dv, 17) = s
                    End If
                  End If
                Next lg1
              End If
            End If
          End If
        End If
      Next lg0
    End If
  Next dv
End Sub

Ça y'est Miloud, l'insomniaque a récupéré, et il est réveillé !

Pour ton énoncé initial, à part ton expression « mettre en mémoire », je l'ai trouvé très clair et très bien expliqué ! j'ai bien suivi toutes les étapes (même si j'ai volontairement parfois un peu changé leur ordre), et je suis bien arrivé au résultat final attendu :

screen

La date du règlement est bien le 27/03/2018, et la référence du virement est bien le n° 12 (ce sont bien les 2 mêmes données qui étaient dans ton fichier initial, que j'avais supprimé manuellement, et qui sont ensuite mises par la macro) ; aussi, je ne comprends pas pourquoi tu dis que ça ne fonctionne pas comme tu le souhaites ! quels sont donc les 2 résultats que tu as obtenus s'ils sont différents ?


Je vais t'expliquer mon code VBA en détail, pour que tu le comprennes mieux (prépare-toi à une très longue lecture !) :

If ActiveSheet.Name <> "TABLEAU DE BORD COLONIES" Then Exit Sub : c'est cette instruction qui fait que la macro se lance uniquement depuis la 1ère feuille "TABLEAU DE BORD COLONIES" ; comme au final les résultats seront sur cette feuille, j'ai trouvé préférable de ne pas lancer la macro depuis la 2ème feuille : tu peux ainsi visualiser les résultats en étant sur la bonne feuille, et si, juste avant de faire Ctrl e, tu es près des colonnes U à X.

Dim bcf As Worksheet, dlig&: Set bcf = Worksheets("BASE COMPTA FOURNISSEURS") : déclare la variable bcf de type feuille ; je l'ai nommée ainsi car ce sont les initiales de la feuille qu'on lui affecte via l'instruction Set : "BASE COMPTA FOURNISSEURS" ; je déclare aussi la variable dlig (dernière ligne) de type long (&).

dlig = bcf.Cells(Rows.Count, 1).End(xlUp).Row: If dlig < 7 Then Exit Sub : je calcule la dernière ligne utilisée de la 2ème feuille, selon la 1ère colonne A intitulée "Compte", puis je sors aussitôt de la sub si dlig est inférieur à 7 ; en effet, c'est inutile de faire tout ce que tu demandes (recherche et suite) si ton GLA est vide, sans aucune ligne de données ! note bien que dans ce cas, dlig vaut 6 car il y a "Compte" en A6 ; j'aurais pu mettre If dlig = 6 mais j'ai préféré prendre des précautions si quelqu'un efface malencontreusement l'intitulé "Compte" (ou d'autres données au-dessus).

Dim c0 As Range, c1 As Range, pmt@, org$, pce$, ltr$, s$, lg0&, lg1&, n&, dv& : déclaration de toutes les autres variables utilisées par cette sub ; c0 sera une cellule de la 2ème feuille ; c1 sera une cellule de la 1ère feuille ; pmt de type monétaire Currency (@) est pour le paiement ; org de type chaîne de caractères ($) est pour l'organisme ; pce est pour le N° pièce ; ltr est pour la lettre (celle utilisée pour le lettrage) ; s de type chaîne de caractères est une « variable de travail » qui servira en fin de sub pour récupérer le n° de la Réf. Justificative (s'il existe) ; lg0 sera un n° de ligne de la 2ème feuille ; lg1 sera un n° de ligne de la 1ère feuille ; n est pour un nombre de lignes ; dv est pour un décalage vertical (ouf ! ça en fait, hein, des variables ! mais j'te rassure : toutes sont utiles, et y'en aura pas d'autres ! ... mais si ça peut t'faire plaisir, t'es entièrement libre d'ajouter des variables inutiles, si tu trouves qu'y'en a pas assez !!! ).

Set c1 = [G5]: n = Cells(Rows.Count, 2).End(xlUp).Row - 5 : n'oublie pas qu'on est sur la 1ère feuille, donc c1 référence la cellule G5 de cette 1ère feuille : c'est la cellule du 1er organisme, ici "CCA - ASLV" ; ensuite, voilà notre nombre de lignes (qui sera utile pour dv) : n est la dernière ligne utilisée, selon la 2ème colonne B intitulée "NOMS", moins 5 (car la 1ère ligne de données de cette feuille commence en ligne 5) : 11 - 5 = 6 (et il y a bien 7 lignes de 0 à 6) ; c'est ce qui va permettre de balayer toutes les lignes de ta FICHE RECAPITULATIVE (= ton « Ainsi de suite en balayant chaque ligne de l'ONGLET TABLEAU DE BORD COLONIES. » ).

Application.ScreenUpdating = 0 : empêche la mise à jour de l'écran pendant l'exécution de la macro ➯ c'est plus rapide

For dv = 0 To n : voilà le décalage vertical de 0 à n, donc de 0 à 6 (7 lignes, car à partir de 0)

pmt = c1.Offset(dv, 14) : comme c1 référence G5 de la 1ère feuille, 14 colonnes à droite ➯ colonne U "PAYEMENT" ; donc grâce au décalage vertical ligne dv, on a le paiement de la bonne ligne, et dv fait que ma description est valable pour chaque ligne.

If pmt > 0 Then : faire la suite seulement si pmt est supérieur à 0

org = c1.Offset(dv): pce = c1.Offset(dv, -1) : on lit l'organisme (pas de décalage colonne, car on est déjà dessus) ; et on lit le N° pièce (une colonne à gauche).

For lg0 = 7 To dlig : rappelle-toi que dlig a été calculé en tout début de sub, juste après le 1er Dim, et sur la 2ème feuille, ça a trouvé 22 ; on va donc lire toutes les lignes du GLA, de 7 à 22 ; note bien que pmt, org, et pce ont été affectés avant d'entrer dans cette boucle (car inutile de les « calculer » à chaque tour de boucle !).

Set c0 = bcf.Cells(lg0, 10) : c0 référence en 2ème feuille la cellule de la ligne lg0, colonne n° 10 ➯ colonne J ; c'est donc la colonne "N° Pièce" du GLA, les décalages horizontaux seront basés sur elle, et l'utilisation de lg0 fait que le raisonnement qui va suivre sera valable pour chaque ligne du GLA.

If c0 = pce Then : on va faire la suite seulement si le "N° Pièce" du GLA correspond à "Pièce N°" de la FICHE (1er impératif).

If c0.Offset(, 2) = org Then : on va faire la suite seulement si l'organisme du "Libellé de l'écriture" (GLA) correspond à l' "ORGANISME" (FICHE) (2ème impératif).

If c0.Offset(, 8) = pmt Then : on va faire la suite seulement si le "Crédit" (GLA) correspond au "PAYEMENT" (FICHE) (3ème impératif).

Note bien qu'à ce stade, les 3 impératifs sont respectés ; c'est cet extrait de ton énoncé : « chercher dans l'onglet "BASE COMPTA FOURNISSEURS" le N° PIECE (ACE03/015), le nom de l'ORGANISME (EARL de ROUVAU) puis le montant au CREDIT (225,00 €) ».

La suite de ton énoncé est : « après cette étape, REGARDER dans la colonne Lettrage s'il existe 1 lettre ».

ltr = c0.Offset(, 6) : on prend donc la lettre de la colonne "Lettrage" (chaîne vide s'il n'y en n'a pas)

If ltr <> "" Then : on va faire la suite seulement si on a trouvé une lettre : tu vois, je "colle" toujours à ton énoncé : « s'il n'y a pas de lettre, donc il n'y a pas de règlement » (➯ rien à faire dans ce cas).

Suite de ton énoncé : « s'il existe une lettre, alors chercher la même lettre dans la colonne LETTRAGE mais au DEBIT. » ; c'est fait avec la boucle qui suit :

For lg1 = lg0 + 1 To dlig : rappelle-toi que lg0 est la ligne en cours, celle sur laquelle on a lu les infos du GLA, dont la lettre (qui à ce stade existe forcément) ; on va donc la rechercher à partir de la ligne en dessous (car lg0 + 1) jusqu'à la dernière ligne dlig ; attention : en faisant cela, j'ai supposé qu'un règlement est forcément après la facture, mais si tu as un cas de règlement avant facture, ça ne sera pas détecté ! exemples : avance sur commande, versement d'un acompte ou d'arrhes (mais si ça avait été le cas, je pense que tu l'aurais mentionné dans ton énoncé détaillé).

Set c0 = bcf.Cells(lg1, 17) : la référence du c0 précédent étant devenue inutile, j'utilise c0 pour référencer la cellule de la ligne lg1, colonne 17 ➯ colonne Q ; c'est donc la colonne "Débit" du GLA (mais oui, j'colle toujours à l'énoncé ! ).

If c0 <> 0 Then : ah, ben oui, sans blague ! si y'a pas de Débit, c'est pas la peine de continuer pour voir la lettre de la ligne !

Donc à ce stade, on a bien trouvé un Débit ! reste à voir si la lettre correspond.

If c0.Offset(, -1) = ltr Then : là, faut se rappeler que la nouvelle référence du c0 était en colonne Q ; donc 1 colonne à gauche, c'est bien évidemment la colonne P, et qu'est-ce qu'on y trouve ? mais oui, bingo ! on est retombé sur la colonne "Lettrage" ; donc le test revient à : est-ce que la lettre correspondant au Débit trouvé est la même que la lettre qui avait été trouvée avant pour le Crédit ? j'crois bien qu'on est en plein dans l'vif du sujet, pas vrai ?

Donc si le test est positif, faire la dernière étape : celle des 2 affectations, pour les 2 résultats.

c1.Offset(dv, 16) = c0.Offset(, -10) : destination : c1.Offset(dv, 16) ➯ 16 colonnes à droite de la colonne G "ORGANISME" : colonne W "DATE REGLMNT" ; source : c0.Offset(, -10) ➯ 10 colonnes à gauche de la colonne Q "Débit" : colonne G "Date Pièce" ; pour la ligne 20 du GLA, c'est bien ton 1er résultat : 27/03/2018 !

Pour les 2 lignes qui suivent, c'est exactement le même principe, mais avec une petite difficulté en plus : le 2ème résultat est une partie du texte d'une cellule de la colonne H "Réf. Justificative" ! j'ai supposé que pour un règlement, il y a systématiquement "Rglt Viremnt", donc je le substitue par "rien", j'enlève avec Trim$ les éventuels espaces à gauche et à droite ➯ reste un éventuel numéro de référence virement, car il n'y en n'a pas toujours !!! voir les 3 exemples au-dessus situés en H11, H13, et H16 : "Rglt Viremnt".

J'sais pas si t'auras eu l'courage de lire tout mon roman ! si oui, t'es vacciné à vie contre les .Offset() de toute sorte !


À te lire pour avoir ton avis ; si tu trouves toujours des résultats inattendus, dis-moi lesquels ils sont ; et selon le détail des explications ci-dessus, dis-moi quelle est d'après toi l'étape qui gêne la bonne obtention des bons résultats, donc quelle partie du code VBA doit être remaniée.

dhany

Effectivement c'est un roman très explicite et je t'en remercie, sa va me permettre surtout de comprendre l'acheminement jusqu'à la réalision finale de mon problème. Sincèrement c'est cool, c'est exactement ce type d'explications qui me fallait pour avancer dans le VBA.

Je vais le décortiquer à l'espace prêt, et je reviens vers toi sans faute.

Merci infiniment pour ton aide précieuse. Je suis persuadé que nous allons réusir à résoudre ce problème.

Je te répondrai surement demain matin, il faut que je trouve la faille

MERCI ENCORE DHANY

J'en reviens pas qu'tu m'aies déjà répondu !!! bonne chance pour trouver la faille (si vraiment il y en a une ! ) ; à demain pour ta future réponse !

dhany

Je suis connecté avec mon smartphone

Je ne pouvais pas attendre jusqu'à demain, l'envie de comprendre te d'apprendre à pris le dessus je crois, je comprends mieux la logique maintenant.

Effectivement, tu as tout à fait raison, tu a totalement respecté l'énoncé et je vais t'avouer que j'ai fais une erreur que je n'ai pas pu voir avant d'envoyer le post. J'ai ce matin essayé d'adapter ton programme à mon fichier complet, et je me suis aperçu que le second impératif celui de l'organisme ne peut pas être traiter en l'état comme celui du montant d'ailleurs avec ton programme.

Je m'explique, après avoir vérifié que cela fonctionne parfaitement avec l'exemple que j'ai transmis dans le post, j'ai voulu adapté ton programme, j'ai essayé de trouver une macro (*) qui permet de copier la base des comptes fournisseurs même fermé pour le coller dans le fichier principal et qui va servir à chercher et trouver les règlements dont il est question. C'est à ce moment là, que j'ai remarqué que la saisie de l'organisme ne correspond pas toujours à celui qui est dans le TABLEAU DE BORD, pour des raisons d'abréviations, des ajouts de parenthèses etc...(exemple avec un autre fournisseur, je cherche ASLV TOURISME ADAPTE et en faite dans la base COMPTA FOURNISSEURS, le nom exact est ASVL (TOURISME ADAPTE), et d'autres exemples comme celui de UFCV et en compta on trouvera U.F.C.V.) c'est à ce moment là que j'ai bien réflechi et effectivement, comme il ne peut y avoir DEUX MEME NUMERO DE PIECE, la recherche peut se faire uniquement avec un seul impératif, celui du n° de pièce et comme la pièce initial est saisi en compta Debit du compte de charge par le crédit du compte fournisseurs, il y a forcément le montant au crédit.

Pour résumer, la recherche dois se faire uniquement sur le N° DE PIECE, mais la logique reste la même, je recherche le N° DE PIECE, je trouve la lettre s'il y a payement, et si payement il y a , on copie la date et le N° du virement dans le TABLEAU DE BORD COLONIES.

Je suis sincèrement et véritablement désolé, je n'ai pas vu cette erreur de ma part.

Que penses tu de mes explications ? Le mieux c'est peut être de t'envoyer le fichier principal, non ?

(*) macro qui permet de copier la base des comptes fournisseurs même fermé pour le coller dans le fichier principal : j'ai trouvé mais ne fonctionne pas correctement comme l'importation du grand livre général...

Une autre question, que veut dire GLA en langage programmation ? je n'ai pas compris cette expression.

bonjour Miloud, le forum,

GLA = Grand Livre Auxiliaire je pense qui correspond à ton tableau dans la base compta frs.

A plus !

Je suis vraiment nul snif

Bonjour Miloud,

Tu a écrit :

que veut dire GLA en langage programmation ? je n'ai pas compris cette expression.

Ah oui, désolé : j'ai oublié de te l'expliquer ; l'ennui c'est que c'est un truc de programmation super compliqué ! bon, essayons quand même : va sur la 2ème feuille "BASE COMPTA FOURNISSEURS", et par exemple en H24, mets cette formule :

=NOMPROPRE(A1)

dhany

C'est fait Dhany

il vient de me mettre "Grand Livre Auxiliaire"

Rechercher des sujets similaires à "fonction recherche avancee possible vba"