Macro Controle existence valeur

[c2h6]

Bonjour,

J'ai deux fichiers Excel différents.
Le premier (List1) et un fichier de travail, le second une extraction d'un système comme base de référence (EXTRACT).
Le fichier EXTRACT est rafraichi automatiquement tous les jours via un système qui le sauvegarde sur un disque définit.
Le fichier List1 est lui alimenté manuellement par copier coller d'un autre système (les 2 systems ne sont pas connecté!).
Je souhaite mettre en place une macro qui va controler l'existance de chacun des articles de la List1 dans EXTRACT. Si l'article a été trouvé alors la zone est coloriée en Vert et sinon en Rouge.
Le fichier EXTRACT est fermé lors du lancement de la macro dans List1.

Merci d'avance pour votre aide

C2H6

 

[job75]

Re : Macro Controle existence valeur

Bonjour à tous, bonjour C2H6 (ethane non ?),

Voici une solution assez simple sans aucune macro et sans besoin d'ouvrir le fichier EXTRACT.

Dans un premier temps, ouvrez les 2 fichiers et activez le fichier List1. Si la taille maximum que peut atteindre la liste de la colonne A est par exemple de 1000 lignes, entrez dans la sélection B1:B1000 la formule :

=SI(ESTTEXTE(A1);SI(ESTNUM(EQUIV(A1;EXTRACT.XLS!$A:$A;0));1;0);"")

Puis appliquez une mise en forme conditionnelle (MFC) sur A1:A1000 avec 2 conditions (et 2 couleurs) et les 2 formules :

= B1=1
= B1=0

C'est donc très simple. Quand EXTRACT est fermé, les formules en B1:B1000 donnent le chemin d'accès de EXTRACT.XLS!$A:$A.

A+

Edit : (supprimer l'espace dans la formule)
pour entrer une formule dans une sélection, faites Ctrl+Entrée.

 

[c2h6]

Re : Macro Controle existence valeur

Re-Bonjour, (oui Ethan en effet!!)

En fait j'ai déjà des formules et mise en forme conditionnelle sur cette colonne dans List1!!
et j'ai besoin de mettre le fichier EXTRACT sur un disque réseau partagé donc je ne vois que la Macro!

Merci pour la solution qui est à l'évidence plus simple mais il me faut une macro!!

Help!!

C2H6

 

[job75]

Re : Macro Controle existence valeur

Si vous devez manipuler le fichier EXTRACT, faites une macro pour cette manipulation. Pour le reste :
- si une mise en forme conditionnelle existe déjà en colonne A ce n'est pas gênant, rajoutez les 2 conditions que j'ai indiquées
- si la colonne B est utilisée, prenez n'importe qu'elle colonne vide, entrez les formules et masquez-la.
A+

Edit : si le nombre de lignes est important, une macro risque de prendre beaucoup de temps pour faire le travail de comparaison des 2 fichiers.

 

[job75]

Re : Macro Controle existence valeur

Ethan je ne vais pas vous laisser mariner plus longtemps. Puisque vous voulez absolument une macro, la voici (à mettre dans ThisWorkbook), elle est très simple :

Private Sub Workbook_Open()
Dim NList1 As Integer, equiv As Integer
Application.ScreenUpdating = False
On Error Resume Next
Workbooks("List1.xls").Activate
Sheets(1).Activate
NList1 = Application.CountA(Range("A:A"))
Range("A:A").ClearFormats
For i = 1 To NList1
equiv = 0
equiv = Application.Match(Cells(i, 1), Workbooks("EXTRACT.xls").Sheets("EXTRACT").Range("A:A"), 0)
Cells(i, 1).Interior.ColorIndex = 3 + Sgn(equiv)
Next
Application.ScreenUpdating = True
End Sub

Attention: cette macro suppose que le fichier EXTRACT est déjà ouvert. Comme ce n'est pas le cas, mettez en 5ème ligne le code d'ouverture de ce fichier, et en avant-avant-dernière ligne le code de fermeture. Pour obtenir ces codes, utilisez l'enregistreur de macro.

A+

Edit 1 : s'il y a un formatage et /ou une MFC en colonne A, remplacez la ligne en rouge par :
Range("A:A").Interior.ColorIndex = xlNone

Edit 2 : supprimer l'espace dans la définition de equiv. Ce système d'édition est pénible, il colle des espaces partout.

 

[c2h6]

Re : Macro Controle existence valeur

Merci!!

C'est que pourquoi faire compliqué quand on peu faire simple.
La première solution sans macro ne marche pas après test, sans doute parceque mon fichier EXTRACT est un faux xls (plutot txt) avec plein de colonnes et celle qui m'interesse n'est pas la première, les format sont differents....

Bref j'ai donc essayé la Macro et après bien des galère j'ai finalement réussi à la faire fonctionner avec le code suivant:

Sub Check_Material()
Dim NList1 As Integer, equiv As Integer
Application.ScreenUpdating = False
On Error Resume Next
'open extract
Workbooks.OpenText Filename:="C:\EXTRACT.xls"
Windows("POM-MRA-BOM UPLOAD PreparationV5.xls").Activate
Range("I4").Select
'fin open extract
'NList1 = Application.CountA(Range("I:I"))
NList1 = Range("I4").SpecialCells(xlCellTypeLastCell).Row
Range("I4:I").Interior.ColorIndex = xlNone
Workbooks("POM-MRA-BOM UPLOAD PraparationV5.xls").Activate
Sheets("Work Sheet").Select
For i = 1 To NList1
equiv = 0
equiv = Application.Match(Cells(i + 3, 9), Workbooks("EXTRACT.xls").Sheets("EXTRACT").Range("BS:BS"), 0)
Windows("POM-MRA-BOM UPLOAD PraparationV5.xls").Activate
Sheets("Work Sheet").Select
Range(i + 3, 9).Select
Cells(i + 3, 9).Interior.ColorIndex = 3 + Sgn(equiv)
Next
'Close extract
Windows("EXTRACT.XLS").Activate
ActiveWindow.Close
Application.ScreenUpdating = True
Workbooks("POM-MRA-BOM UPLOAD PraparationV5.xls").Activate
End Sub


Merci encore pour votre aide et longue vie à ce forum!

C2H6

 

[Staple1600]

Re : Macro Controle existence valeur

Bonjour à tous


--> job75: Peux-tu me confirmer, stp,

si ces modifications dans ton code produisent le même résultat que ta macro initiale.

Merci.

Sub test_code_job75()
'Déclarations
Dim i As Long
Dim NList1 As Integer, equiv As Integer
Application.ScreenUpdating = False
On Error Resume Next
[COLOR=seagreen]'ouverture Extract.xls[/COLOR]
Workbooks.Open Filename:="C:\Temp\Extract.xls"
[COLOR=seagreen]'traitement de la feuille 1, colonne A[/COLOR]
With Workbooks("List1.xls").Sheets(1)
    NList1 = Application.CountA(.Range("A:A"))
[COLOR=seagreen]'effacement Format[/COLOR]
        .Range("A:A").ClearFormats
            For i = 1 To NList1
            equiv = 0
            equiv = _
            Application.Match(Cells(i, 1), _
            Workbooks("EXTRACT.xls").Sheets("EXTRACT").Range("A:A"), 0)
        .Cells(i, 1).Interior.ColorIndex = 3 + Sgn(equiv)
Next
End With
Application.ScreenUpdating = True
End Sub

 

[c2h6]

Re : Macro Controle existence valeur

Re-Bonjour,

J'ai essayé ton code mais sa ne marche pas.
Le problème c'est que la macro reste sur la feuille du fichier EXTRACT qu'elle vient d'ouvrir et donc l'étape pour déterminer Nlist1 donne 0.
NList1 = Application.CountA(.Range("I:I")) ne marche pas.

C2H6

 

[job75]

Re : Macro Controle existence valeur

Bonjour tout le monde,

Staple : le fichier Extract.xls ne s'ouvre pas.

Ethan, c'est vrai VBA n'est pas évident au début, j'indique en rouge ce qui ne paraît pas normal sur votre macro :
Nota :à la fin, vous pouvez écrire simplement : Windows("EXTRACT.XLS").Close

Sub Check_Material()
Dim NList1 As Integer, equiv As Integer
Application.ScreenUpdating = False
On Error Resume Next
'open extract
Workbooks.OpenText Filename:="C:\EXTRACT.xls"
Windows("POM-MRA-BOM UPLOAD PreparationV5.xls").Activate
Range("I4").Select'Utile ? Activer d'abord la feuille
'fin open extract
'NList1 = Application.CountA(Range("I:I"))
NList1 = Range("I4").SpecialCells(xlCellTypeLastCell).Row
Range("I4:I").Interior.ColorIndex = xlNone'Range incorrect, écrire "I:I"
Workbooks("POM-MRA-BOM UPLOAD PraparationV5.xls").Activate'Inutile (déjà activé)
Sheets("Work Sheet").Select'A mettre après l'activation du fichier
For i = 1 To NList1'Ecrire For i = 4 et remplacer i + 3 par i plus bas
equiv = 0
equiv = Application.Match(Cells(i + 3, 9), Workbooks("EXTRACT.xls").Sheets("EXTRACT").Range(" BS:BS"), 0)
Windows("POM-MRA-BOM UPLOAD PraparationV5.xls").Activate'Inutile
Sheets("Work Sheet").Select'Inutile
Range(i + 3, 9).Select'A supprimer
Cells(i + 3, 9).Interior.ColorIndex = 3 + Sgn(equiv)
Next
'Close extract
Windows("EXTRACT.XLS").Activate
ActiveWindow.Close
Application.ScreenUpdating = True
Workbooks("POM-MRA-BOM UPLOAD PraparationV5.xls").Activate'Inutile
End Sub

A+

 

[c2h6]

Re : Macro Controle existence valeur

Re

J'ai appliqué tes modifications pour voir...
La macro ne marche plus car elle reste sur le fichier EXTRACT et ne revient pas dans l'autre. La conséquence c'est que Nlist = nombre de ligne de EXTRACT au lieu de l'autre, la comparaison est fausse et le cellule mise à jour sont dans EXTRACT.

Mystère!!!

 

[job75]

Re : Macro Controle existence valeur

Essayez en remplaçant le 1er Windows par Workbooks. A+

 

[Staple1600]

Re : Macro Controle existence valeur

Re

Workbooks.Open Filename:="C:\[COLOR=Blue][B]Temp[/B][/COLOR]\Extract.xls"

(j'ai modifié le chemin pour tester plus facilement)

Et j'ai ajouté cette ligne dans ton code (job75) Puisque c2h6 l'ouvrait dans son
message de 6h12.


job75: Je voulais avoir ton avis pour savoir si mes modifications
étaient correctes au niveau de la syntaxe VBA.

 

[job75]

Re : Macro Controle existence valeur

Staple : pardon, j'avais mal compris, je pensais que vous vouliez tester l'ouverture avec le dossier Temp. J'ai retesté, et constaté que With...End seul ne permet pas d'obtenir les modifs sur le fichier List1. Il faut d'abord l'activer. A+

 

[Staple1600]

Re : Macro Controle existence valeur

Re


Merci job75

En effet cela change tout 😱

Sub test_code_job75_bis()
'Déclarations
Dim i As Long
Dim NList1 As Integer, equiv As Integer
Application.ScreenUpdating = False
On Error Resume Next
'ouverture Extract.xls
Workbooks.Open Filename:="C:\Temp\Extract.xls"
'traitement de la feuille 1, colonne A
Workbooks("List1.xls").Activate
With Sheets(1)
    NList1 = Application.CountA(.Range("A:A"))
'effacement Format
        .Range("A:A").ClearFormats
            For i = 1 To NList1
            equiv = 0
            equiv = _
            Application.Match(Cells(i, 1), _
            Workbooks("EXTRACT.xls").Sheets("EXTRACT").Range("A:A"), 0)
        .Cells(i, 1).Interior.ColorIndex = 3 + Sgn(equiv)
Next
End With
Application.ScreenUpdating = True
End Sub

 

[job75]

Re : Macro Controle existence valeur

Ok Staple, pas de problème, mais pourquoi utiliser With...End With, c'est plus simple avec :
Workbooks("List1.xls").Sheets(1).Activate... A+

Edit : il faut aussi que Sheets(1) soit préalablement activée...!