Code vba rapide avec la rechercheV pour prendre des données depuis une feuille et les coller dans une autre

Version imprimable

3 pièce(s) jointe(s)

Bonjour à tous le forum. J'espère que vous allez tous bien. Je suis nouveau dans le forum by the way.

Je dois développer une macro dans le cadre du développement des outils de mon entreprise. Il s'agit du suivi des créances clients. L'outil doit permettre de mettre à jour les soldes des comptes clients, tout en gardant les montants historiques des quatre derniers jours. Bon assez d'explications, mon problème est le suivant:

Pièce jointe 620325
- le classeur Excel contient deux feuilles intitulées respectivement "Comptes clients " et "Comptes histo". Les deux feuilles ont la même structure: ils contiennent des tableaux avec des colonnes intitulées *numéro du compte (col. E), *intitulé du compte, 4 colonnes *montants (col. K à O) avec les dates corresp. sur les entêtes . On doit être capable de mettre à jour les soldes dans "Comptes clients" et reprendre les soldes anciens correspondant dans les colonne K à N de "Comptes histo" et les coller dans la feuille "Comptes clients" à côté des soldes à jour. Pour cette dernière tâche j'ai trouvé le code ci-dessous sur internet, qui utilise la recherchev.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Sub prendredonnées() Dim plage As Range, plrech As Range, C As Range, Teste With Sheets("Comptes Clients") Set plage = .Range(.Cells(15, 5), .Cells(.Rows.Count, 5).End(xlUp)) End With With Sheets("Comptes histo") Set plrech = .Range(.Cells(15, 5), .Cells(.Rows.Count, 15).End(xlUp)) End With For Each C In plage Teste = Application.VLookup(C.Value, plrech, 6, False) If Not IsError(Teste) Then C.Offset(, 7).Value = Teste Else C.Offset(, 7).Value = "0" End If Next C End Sub
Cependant, l'exécution est diablement lente (30 minutes minimum, plus qu'il ne prend pour le traitement manuel :lol: A noter que la taille des comptes est volumineux (près de 65 000 lignes).
Si vous pouviez me suggérer une autre façon de faire, ça serait vraiment libérateur.

Merci d'avance.

05/06/2022, 18h40
Philippe Tulliez
Bonjour,
Pourquoi une boucle sur chaque ligne.
En VBA, il est possible d'écrire une seule ligne de code qui écrit la même formule dans la colonne entière
Il suffit d'écrire la formule manuellement et après l'avoir testée, l'insérer dans le code VBA

Voir mes deux billets sur le sujet
- Ecrire une formule dans Excel à l'aide d'une procédure VBA
- Comment écrire une formule plus complexe dans une cellule ?

Bonjour, merci de votre réponse.
Mais si j'essayais cela, comment vais-je spécifier l'argument valeur cherchée de la rechercheV? qui change en fonction du numero de ligne.
Dans la première formule, je pouvais faire C.value.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub prendredonnées()
 
    Dim plage As Range, plrech As Range, C As Range, Teste
        With Sheets("Comptes Clients")
            Set plage = .Range(.Cells(15, 5), .Cells(.Rows.Count, 5).End(xlUp))
        End With
        With Sheets("Comptes histo")
            Set plrech = .Range(.Cells(15, 5), .Cells(.Rows.Count, 15).End(xlUp))
        End With
 
            plage.Offset(, 7).formula= "=VLookup(plage.Value, plrech, 6, False)"
 
End Sub

06/06/2022, 07h40
Philippe Tulliez

Bonjour,

Citation:

Mais si j'essayais cela, comment vais-je spécifier l'argument valeur cherchée de la rechercheV? qui change en fonction du numero de ligne.

Votre question prouve que vous n'avez pas lu les deux billets que je vous ai suggéré de consulter
Avez-vous essayé d'écrire cette formule manuellement dans excel ?

Petite remarque
Si je lis votre code, vous définissez votre plage de la feuille nommée "Comptes Clients" à partir de la colonne 5 de la ligne 5 or si je me réfère à votre illustration, la première ligne des données du tableau présente dans cette feuille commence en ligne 13 et pour la plage de l'autre feuille cela me semble également incohérent entre l'illustration et votre code.
06/06/2022, 10h19
BENNASR

Bonjour MYBAD, Philippe Tulliez, la forum
A mon modeste avis, ce n'est pas la bonne conception pour gérer tes clients, d'ailleurs pourquoi les dates comme titre de colonne !!!!
il suffit d'insérer une seule colonne nommée DATE et tu insère la date devant chaque mouvement
Aussi je ne comprends pas :
1 - Pourquoi deux onglets, une seule onglet et tu filtre avec une colonne date ne suffit pas ?
2 - Pourquoi du VBA : il suffit de travailler avec un tableau structuré et tu insère un tableau croisée dynamique avec des segments et tu aura tout ce qui tu cherches avec une belle présentation

Citation:

Envoyé par Philippe Tulliez

Bonjour,
Votre question prouve que vous n'avez pas lu les deux billets que je vous ai suggéré de consulter
Avez-vous essayé d'écrire cette formule manuellement dans excel ?

Bonjour,
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Sub prendredonnees() Dim plage As Range, plrech As Range, C As Range 'déclaration des variables formule et valeur cherchée Dim myFormula As String, newFormula As String, Vcher As Range With Sheets("Comptes Clients") Set plage = .Range(.Cells(15, 5), .Cells(.Rows.Count, 5).End(xlUp)) End With With Sheets("Comptes histo") Set plrech = .Range(.Cells(15, 5), .Cells(.Rows.Count, 15).End(xlUp)) End With Set Vcher = Range("E15") Set Vcher = Vcher.Offset(1, 0) myFormula = "=VLookup(<Vcherche>, <Precherche>,7, False)" newFormula = Replace(Replace(myFormula, "<Precherche>", plrech.Address), "<Vcherche>", Vcher.Address) plage.Offset(, 7).Formula = newFormula If IsError(newFormula) Then plage.Value = "0" End If End Sub End Sub
J'ai revu mon code, qui marche bien à la remarque près que je ne sais pas comment rendre dynamique la référence de la cellule contenant la valeur cherchée. J'ai bien lu vos deux articles Phillipe, je vous en remercie, mais je suis coincé...:oops:

Citation:

Petite remarque
Si je lis votre code, vous définissez votre plage de la feuille nommée "Comptes Clients" à partir de la colonne 5 de la ligne 5 or si je me réfère à votre illustration, la première ligne des données du tableau présente dans cette feuille commence en ligne 13 et pour la plage de l'autre feuille cela me semble également incohérent entre l'illustration et votre code.

Vous inquiétez pas, c'est les bonnes références que j'ai mis. La capture que je vous ai envoyée est un peu truqué pour la confidentialité.
Merci.

08/06/2022, 19h57
MYBAD

Bonjour Bennasr.

Il se pourrez que j'aie pas été clair dans mes explications.

Merci pour votre contribution quand même.