Code VBA rechercher cellule

Version imprimable

20/01/2021, 01h23
Moun10be

2 pièce(s) jointe(s)

Code VBA rechercher cellule

Bonjour,

Dans le cadre de mon boulot j'essai de faciliter les taches de mes collègues en essayant d'améliorer certain fichier Excell

1) comment faire apparaitre en couleur la cellule qui ce trouve dans la colonne A en fessant une recherche sur la cellule L20
Ex : je tape T4060 sur la cellule L20 et la cellule A6 prend une couleur genre orange

2) Lorsque j'ai un type de motrice indiquer dans les cellules de la colonne A les cellules M4, M6, M8 et M10 totalise les types de motrices qui on été indiqué dans les cellules de la colonne A
Ex : il est inscrit dans les cellules de la colonne A T4*** alors la cellule M10 totalise le nombre de motrice qui on été inscrit dans les cellules de la colonne A
Ainsi de suite si on indique T2*** la cellule M6, T3*** la cellule M8, T7*** la cellule M4 totalise de la même manière.
Et la cellule M13 totalise le nombre qui est indiquer dans les cellules M4, M6, M8 et M10

Je vous joint le fichier Excell ==> Pièce jointe 588901

Un tout grand merci d'avance.

Pièce jointe 588900

Bonjour,

Voici
Pièce jointe 588902

Normalement, pas besoin de VBA, mais comme c'est votre demande, alors:
A chaque appui sur le bouton "DELETE", les formules sont réécrites ainsi que la mise en forme conditionnelle sur la colonne A

Le code

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
Sub Delet()
    Range("A3:J59").Select
    Selection.ClearContents
    Formules
    MFC
End Sub
 
Sub Formules()
    Application.ScreenUpdating = False
    Range("M4").FormulaR1C1 = "=COUNTIF(C1,""T7*"")"
    Range("M6").FormulaR1C1 = "=COUNTIF(C1,""T2*"")"
    Range("M8").FormulaR1C1 = "=COUNTIF(C1,""T3*"")"
    Range("M10").FormulaR1C1 = "=COUNTIF(C1,""T4*"")"
    Range("M13").FormulaR1C1 = "=R[-9]C+R[-7]C+R[-5]C+R[-3]C"
End Sub
 
Sub MFC()
    Dim DerLig As Long
    Formule = "=$A3=$L$20"
    DerLig = Range("A" & Rows.Count).End(xlDown).Row
    Range("A3").Select
    'Efface tous les formats conditionnels existants sur toute la plage sélectionnée
    Range("A3:A" & DerLig).FormatConditions.Delete
    'Ajoute un Format conditionnel
    Range("A3:A" & DerLig).FormatConditions.Add(xlExpression, xlLess, Formule).Interior.Color = RGB(255, 192, 0)
End Sub

Cdlt

20/01/2021, 05h59
Philippe Tulliez
Bonjour,

Citation:

1) comment faire apparaitre en couleur la cellule qui ce trouve dans la colonne A en fessant une recherche sur la cellule L20
Ex : je tape T4060 sur la cellule L20 et la cellule A6 prend une couleur genre orange

Une simple mise en forme conditionnelle suffit.

Les étapes
- Sélection des cellules de la colonne A (de A3 à A?)
- Cliquer sur Nouvelle règle... dans la liste déroulante de la commande Mise en forme conditionnelle du groupe Style de l'onglet [Accueil]
- Dans la boîte de dialogue Nouvelle règle de mise en forme, sélectionner la règle Utilisez une formule pour déterminer pour quelles cellules le format sera appliqué et entrer la formule suivante
  
  Code:
  
  A3=$L$20
- Définir le format à appliquer si la formule renvoie VRAI
Je vous suggère d'utiliser les tableaux structurés.
1. Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)
2. Excel: Les tables de données en VBA
Le second tutoriel vise l'utilisation par VBA des tableaux structurés
20/01/2021, 10h33
Moun10be

1 pièce(s) jointe(s)

Merci Pilippe et ARTURO83

De préférence j'aimerai en code VBA :D

Arturo83 tes code sont parfait, par contre comment je pourrai corriger cette erreur dans le code

Je m'explique lorsque j'inscrit une recherche dans la cellule L20 c'est bon
Mais lorsque rien n'est inscrit dans la cellule L20 alors toute les cellules de la colonne A sont en orange

Merci.

Pièce jointe 588936
20/01/2021, 10h55
PrinceCorwin

Bonjour,

Sur la mise forme conditionnelle, il faut ajouter le fait de tester si la cellule L20 est vide

Code:

=ET(A3=$L$20;NON(ESTVIDE($L$20)))
20/01/2021, 11h03
ARTURO83

Dans la Sub MFC()

remplacez

Code:

Formule = "=$A3=$L$20"

par

Code:

Formule = "=ET($L$20<>"""";$A3=$L$20)"

Cdlt
20/01/2021, 11h09
Moun10be

Merci :D
20/01/2021, 11h36
Moun10be

1 pièce(s) jointe(s)

Ah oui si ce n'est pas trop demander :oops:
j'aimerai en code VBA, que lorsque j'inscrit Sable dans les cellules H3 à H59 qu'il me le colorie en jaune les cellules ou il est inscrit sable et me le totalise dans la cellule M16

Ex : si j'ai 3 sable d'inscrit dans les cellules H il m'inscrit le nombre 3 dans la cellule M16

Merci encore.

Pièce jointe 588943
20/01/2021, 11h53
Philippe Tulliez

Bonjour,

Citation:

j'aimerai en code VBA, que lorsque j'inscrit Sable dans les cellules H3 à H59 qu'il me le colorie en jaune les cellules ou il est inscrit sable et me le totalise dans la cellule M16

Ce que vous cherchez à faire peut être fait sans VBA avec des formules natives d'excel

Vous pouvez aussi effectuer la somme d'une plage avec VBA mais n'est-ce pas un peu ridicule ?

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
Voici, remplacez tout le code précédent par celui-ci
 
Dim DerLig As Long
 
Sub Delet()
    Application.ScreenUpdating = False
    Range("A3:J59").ClearContents
    Formules
    MFC_Vehicules
    MFC_Colonne_Sables
End Sub
 
Sub Formules()
    Application.ScreenUpdating = False
    DerLig = Range("A" & Rows.Count).End(xlDown).Row
    Range("M4").FormulaR1C1 = "=COUNTIF(C1,""T7*"")"
    Range("M6").FormulaR1C1 = "=COUNTIF(C1,""T2*"")"
    Range("M8").FormulaR1C1 = "=COUNTIF(C1,""T3*"")"
    Range("M10").FormulaR1C1 = "=COUNTIF(C1,""T4*"")"
    Range("M13").FormulaR1C1 = "=R3C+R[-7]C+R[-5]C+R[-3]C"
    Range("M16").FormulaR1C1 = "=COUNTIF(R3C8:R" & DerLig & "C8,""Sable"")"
End Sub
 
Sub MFC_Vehicules()
    Dim DerLig As Long
    Formule = "=ET($L$20<>"""";$A3=$L$20)"
    DerLig = Range("A" & Rows.Count).End(xlDown).Row
    Range("A3").Select
    'Efface tous les formats conditionnels existants sur toute la plage sélectionnée
    Range("A3:A" & DerLig).FormatConditions.Delete
    'Ajoute un Format conditionnel
    Range("A3:A" & DerLig).FormatConditions.Add(xlExpression, xlLess, Formule).Interior.Color = RGB(255, 192, 0) 'Orange
End Sub
 
Sub MFC_Colonne_Sables()
    Application.ScreenUpdating = False
    On Error Resume Next
    '*************************************************************************************
    Dim Plage_MFC As Excel.Range, FC1 As Excel.FormatCondition, FC2 As Excel.FormatCondition, FC3 As Excel.FormatCondition, FC4 As Excel.FormatCondition
 
    DerLig = Range("A" & Rows.Count).End(xlDown).Row
    Range("H3").Select
    Set Plage_MFC = Range("H3:I" & DerLig)
    Plage_MFC.FormatConditions.Delete
 
    Set FC1 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""vh s3*""") 'Bleu clair
    FC1.Interior.Color = RGB(153, 204, 255)
    FC1.Font.Color = RGB(0, 0, 0)
 
    Set FC2 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""Sable""") 'Vert
    FC2.Interior.Color = RGB(0, 255, 0)
    FC2.Font.Color = RGB(0, 0, 0)
 
    Set FC3 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""Collision""") 'Orange
    FC3.Interior.Color = RGB(255, 102, 0)
    FC3.Font.Color = RGB(255, 255, 255)
 
    Set FC4 = Plage_MFC.FormatConditions.Add(Type:=xlExpression, Formula1:="=$H3=""Lav s2/s3*""") 'Bleu clair
    FC4.Interior.Color = RGB(153, 204, 255)
    FC4.Font.Color = RGB(0, 0, 0)
 
    '***** réinitialisation des variables *******************************************
    Set FC1 = Nothing
    Set FC2 = Nothing
    Set FC3 = Nothing
    Set FC4 = Nothing
    Set Plage_MFC = Nothing
End Sub

20/01/2021, 13h12
Moun10be

1 pièce(s) jointe(s)

Merci Arturo83
par contre il me colorie aussi les cellules de la colonne I, comment l'enlever pour qu'il me colorie juste les cellules de la colonne H

Merci

Pièce jointe 588949
20/01/2021, 13h16
Moun10be

Citation:

Envoyé par Philippe Tulliez

Bonjour,

Ce que vous cherchez à faire peut être fait sans VBA avec des formules natives d'excel

Vous pouvez aussi effectuer la somme d'une plage avec VBA mais n'est-ce pas un peu ridicule ?

Bonjour Philipe,

Ridicule peut être, je l'ai déjà en formule mais je le voulais en VBA pour le savoir cela me permet en regardant les codes VBA que notre ami ARTURO83 me propose de comprendre un peu :)

En plus je trouve la feuille plus propre même si c'est vrai que je peu cacher les colonnes.

Merci.
20/01/2021, 13h33
PrinceCorwin

Bonjour,

Je me permet une remarque.
Rien n'est ridicule, c'est d'ailleurs le "leitmotiv" du forum, l'entraide, le partage du savoir !
Mais cela pose des questionnements ou des interrogations sur les intentions finales des demandeurs pour des personnes plus confirmées. Et je ne me compte pas dans cette dernière catégorie :)

Avec le code VBA, il faut une action manuelle afin de lancer les calculs, avec le bouton "DELETE", car ce sont des sous-programmes (SUB), ou passer par la fenêtre d'exécution des macros (ALT+F8).
En mode fonction, on automatise les calculs, sauf cas exprimer si le classeur est en mode manuelle. Dans ce cas, il faut appuyer sur F9 pour recalculer le classeur.

Si vous souhaitez avoir du code VBA, pour apprentissage, enregistrez les macros.
Personnellement, c'est ce que je fais. Et je pense, sans me tromper, que beaucoup en font autant.
Vous aurez les codes VBA correspondant aux formules et autres actions que vous pourrez réaliser pendant l'enregistrement.
Ne reste qu'à modifier le codes pour qu'il soit "portable". Automatiser les sélections, la lecture d'informations sur les cellules, et bien d'autres.

Bien amicalement
20/01/2021, 15h37
ARTURO83

Auparavant effacer les MFC de la colonne I
Remplacez cette ligne dans Sub MFC_Colonne_Sables() :

Code:

Set Plage_MFC = Range("H3:I" & DerLig)

par

Code:

Set Plage_MFC = Range("H3:H" & DerLig)

Cdlt
20/01/2021, 16h39
Philippe Tulliez

Bonjour,

Citation:

Ridicule peut être, je l'ai déjà en formule mais je le voulais en VBA pour le savoir cela me permet en regardant les codes VBA que notre ami ARTURO83 me propose de comprendre un peu

Ma remarque avait pour but d'expliquer qu'il est bien plus profitable d'exploiter les fonctionnalités natives d'excel car à quoi bon avoir des fonctions permettant de faire des recherches, des dénombrements suivant conditions, des mises en forme conditionnelles, etc. si c'est pour refaire en VBA ce qui existe et qui peut être mis en place bien plus rapidement.

Comme l'a soulevé "PrinceCorwin", que je salue au passage, pour apprendre le VBA, il suffit d'utiliser l'enregistreur de macros et même s'il faut ensuite rectifier une bonne partie de son code, on connait au moins les propriétés et méthodes de l'objet que l'on manipule.

Je vous ai indiqué de vous intéresser aux tableaux structurés en vous donnant les liens et pas une réaction alors que leur usage facilite grandement l'emploi d'excel et encore plus si on doit programmer en VBA.
Chacun est bien entendu libre d'agir comme il l'entend mais quand j'aide quelqu'un, j'essaye que celui-ci soit le mieux aiguiller plutôt que de le maintenir dans l'ignorance.
Est-ce que pour maintenir un tram, vous accepteriez que l'on utilise de vieux outils ou que l'on continue à contrôler les appareils à l'ancienne ?
20/01/2021, 23h08
Moun10be

Citation:

Envoyé par Philippe Tulliez

Je vous ai indiqué de vous intéresser aux tableaux structurés en vous donnant les liens et pas une réaction alors que leur usage facilite grandement l'emploi d'excel et encore plus si on doit programmer en VBA.
Chacun est bien entendu libre d'agir comme il l'entend mais quand j'aide quelqu'un, j'essaye que celui-ci soit le mieux aiguiller plutôt que de le maintenir dans l'ignorance.
Est-ce que pour maintenir un tram, vous accepteriez que l'on utilise de vieux outils ou que l'on continue à contrôler les appareils à l'ancienne ?

Et je vous en remercie d'avance à chacun d'entre vous, je suis tout a fais d'accord avec vous je voulais juste les avoir en code VBA par curiosité et savoir, j'ai conscience qu'il y a possibilité de le faire avec des formules natif et en l'enregistrant les macro, mais étant novice dans les deux possibilité surtout en code VBA, mon intention était juste d'avoir vôtres aides toute en essayent de comprendre comment vous l'avez fait, ce que ARTURO83 à bien fait dans ces lignes de code ou il précise chaque macro correspond a quoi.

Après je ne pourrais jamais prétendre arriver à un bon niveau, pour cause je suis juste un simple novice amateur d'Excel, je suis technicien Tram j'utilise Excel juste pour donner des suivis de mes avaries ou développé des petits fichier pour mes collègues d'autres service (Grace a vôtres aides)

Merci encore pour tout.
20/01/2021, 23h31
Moun10be

Citation:

Envoyé par ARTURO83

Auparavant effacer les MFC de la colonne I
Remplacez cette ligne dans Sub MFC_Colonne_Sables() :

Code:

Set Plage_MFC = Range("H3:I" & DerLig)

par

Code:

Set Plage_MFC = Range("H3:H" & DerLig)

Cdlt

Bonjour,
J'ai pas bien compris lorsque tu me demande d'effacer les MFC de la colonne I

Par contre j'ai bien remplacé la ligne
Set Plage_MFC = Range("H3:I" & DerLig)
Par celui là ==> Set Plage_MFC = Range("H3:H" & DerLig)
Mais le souci est toujours présent
21/01/2021, 01h34
Moun10be

Bonjour,

Vous allez peut être me trouver un peut bête mais bon j'ai déjà fait quelques mise en forme mais pour celui là je rame, je m'explique j'essaie que les cellules de la colonne F3 à F78 devient jaune si la valeur est inferieure à 13h00 dans les cellules de la colonne F

Voila la procédure que j'ai suivi
Nouvelle règle de mise en forme ==> Appliquer une mise en forme uniquement aux cellules qui contiennent ==> valeur de la cellule ==> inferieure à ==> puis la formule que je place ne doit être correcte ==> =$F$3:$F$78>12:00:00 :oops:
21/01/2021, 03h39
ARTURO83

Bonjour,

Citation:

J'ai pas bien compris lorsque tu me demande d'effacer les MFC de la colonne I

parce qu'il y avait des MFC qui traînaient dans cette colonne.

Pour la MFC dans la colonne F, il faut rentrer l'heure sous format numérique c'est à dire pour 13h00 ,il faut écrire 13/24 (1heure =1/24)

Voilà avec le fichier c'est plus simple
Pièce jointe 588998

Cdlt
21/01/2021, 11h07
Moun10be

Citation:

Envoyé par ARTURO83

Bonjour,
parce qu'il y avait des MFC qui traînaient dans cette colonne.

Pour la MFC dans la colonne F, il faut rentrer l'heure sous format numérique c'est à dire pour 13h00 ,il faut écrire 13/24 (1heure =1/24)

Voilà avec le fichier c'est plus simple
Pièce jointe 588998

Cdlt

Super merci ARTURO83
27/01/2021, 15h23
Moun10be

1 pièce(s) jointe(s)

Bonjour,

mes collègues mon demandé s'il était possible.

1) Que chaque type de véhicule qui ce trouve dans la colonne C soit totaliser sur les cellules correspondantes

Ex: s'il y a inscrit 10 fois le nombre 7 dans les cellules de la colonne C alors le nombre 10 s'inscrit dans la cellule M26
s'il y a inscrit 8 fois le nombre 8 dans les cellules de la colonne C alors le nombre 8 s'inscrit dans la cellule M28
s'il y a inscrit 12 fois le nombre 30 dans les cellules de la colonne C alors le nombre 12 s'inscrit dans la cellule M30
s'il y a inscrit 5 fois le nombre 40 dans les cellules de la colonne C alors le nombre 5 s'inscrit dans la cellule M26

2) Que lorsque l'heure de fin est inferieure à 14h dans les cellules de la colonne F alors il me comptabilise le nombre et me l'inscrit dans la cellule M35

Merci d'avance.

Pièce jointe 589470
27/01/2021, 16h23
Philippe Tulliez

Bonjour,
Il est possible de tout faire et 97% sans VBA.
Même pour indiquer type de véhicule 8 pour les trams T2000. Il suffit de passer par une table de conversion.

J'ai déjà écrit dans ma réponse (fil ) qu'il vaudrait mieux convertir vos listes de données en tableaux structurés.
Si par la suite, vous voulez faire une fiche technique pour chaque modèle (nombre de places assises, longueur, poids du véhicule, vitesse maximale, bogies, etc. rien de plus simple, il suffit d'ajouter une nouvelle table structurée et de la reliée avec des fonctions de recherches INDEX, EQUIV, RECHERCHEV.

Je trouve plus simple de faire le maximum avec les fonctionnalités et fonctions natives d'excel que de développer en VBA.

[EDIT]
J'ajouterai qu'en quelques clics et à l'aide du tableau croisé dynamique, vous aurez le résultat attendu et une fois ce tableau construit, une simple actualisation vous donne le résultat si de nouvelles lignes sont ajoutées (à l'aide d'un tableau structuré)
29/01/2021, 20h32
Moun10be
Bonjour,
J'ai protégé la feuille avec cette ligne de code
Code:

1 2 3 4 5 6 7 Private Sub Workbook_Open() With Sheets("Rentrée_Sortie") .Protect "test", UserInterfaceOnly:=True End With End Sub
Par contre que dois-je rajouter dans le code VBA afin de pouvoir modifier les couleur de police et remplissage sans pour autant déprotéger la feuille.

Merci.