PARCOURIR UNE COLONNE ET COMPARER LES VALEURS EN VBA

[jamisca]

bonjour,
je veux faire un module qui affichera un message à chaque ouverture de mon fichier excel.
Le programme se présentera comme suit, j'ai plusieurs colonnes dont une correspond a la date d'expiration et l'autre la marque et une autre à la valeur..

-PARCOURIR LES CELLULES DE LA COLONNE DATES D'EXPIRATION DES PRODUITS
- SI LA DATE D'EXPIRATION = AUJOURD'HUI - 3 JOURS ALORS AFFICHER LA MARQUE ET LA VALEUR DU PRODUIT.
SI NON SI LA DATE D'EXPIRATION = AUJOURD'HUI ALORS AFFICHER MESSAGE D'ALERTE AVEC LES REFERENCES DU PRODUIT.

Merci,

---

Moderator Note: I moved this Thread from where it was originally posted, ( Rick Rothstein's Corner ).

[DocAElstein]

Hello, Bonjour jamisca,
Welcome to Excel Fox…
I can't speak French, so I am just working on this Google translation
( https://translate.google.de/?hl=de#v...20DU%20PRODUIT ) :
I want to make a module that will display a message every time I open my excel file.
The program will be as follows, I have several columns one of which corresponds to the expiry date and the other the mark and another to the value.

-PARCOURING COLUMN CELLS EXPIRATION DATES OF PRODUCTS
- IF THE EXPIRATION DATE = TODAY - 3 DAYS THEN DISPLAY THE BRAND AND VALUE OF THE PRODUCT.
IF NO IF THE EXPIRATION DATE = TODAY THEN DISPLAY ALERT MESSAGE WITH PRODUCT REFERENCES

---

In Excel there are already written for us macro routines which run every time a workbook is opened. But there are no code lines in them. So they run, but nothing happens. So we have to add code lines into these routines, to do what we want to happen when the workbook is opened :

Here you will find the routine you need
_ Hit keys, Alt+F11 , to get the VB Editor window
_ Now double click on something like ThisWorkbook, or DieseArbeitsmappe , or maybe something similar to Cecahier or ceclasseur
ThisWorkbook code module.JPG : https://imgur.com/f9qHVqP
ThisWorkbook code module.jpg
_ Next use drop down lists to select the available routine
ThisWorkbook Workbook.JPG : https://imgur.com/Q6jkYIF
ThisWorkbook Open.JPG : https://imgur.com/3lZSTPe
As you can see from the last screenshot, the routine, Sub Workbook_Open() , has no code lines in it. This routine always runs when the workbook is opened, but nothing happens because there are no code lines in it.
Within the routine you can add code lines which will be run every time the Workbook is open.

---

So, now I will give you a sample routine. It probably does not do exactly what you want, but you may be able to modify it to suit your exact requirements.
Here is a sample file:
http://www.excelfox.com/forum/showth...ll=1#post11362
http://www.excelfox.com/forum/showth...1189#post11189


Try this routine:

Option Explicit
Private Sub Workbook_Open()
Dim DteLng As Long, DteAujourd_hui As Long  ' VBA holds day date as a whole number
 Let DteAujourd_hui = Now
Dim arrData() As Variant ' The reason I need Variant is that the next line allows me to capture an entire range in one go, using the .Value property which returns the values in Variant type elements. So the type definition must match.
 Let arrData() = ThisWorkbook.Worksheets.Item(1).Range("A1").CurrentRegion.Value2

Dim Cnt As Long
    For Cnt = 2 To UBound(arrData(), 1)
    ' IF THE EXPIRATION DATE = TODAY - 3 DAYS THEN DISPLAY THE BRAND AND VALUE OF THE PRODUCT
        If arrData(Cnt, 1) = DteAujourd_hui - 3 Then MsgBox Prompt:="Expirey date today-3 " & arrData(Cnt, 2) & " , " & arrData(Cnt, 3)
    ' IF THE EXPIRATION DATE = TODAY THEN DISPLAY ALERT MESSAGE WITH PRODUCT REFERENCES
    Dim AlertMsg As String '
       If arrData(Cnt, 1) = DteAujourd_hui Then Let AlertMsg = AlertMsg & vbCr & vbLf & arrData(Cnt, 2) ' Build up long string of products expiring today
    Next Cnt

    If AlertMsg <> "" Then MsgBox Prompt:="Today's Expire:" & vbCr & vbLf & AlertMsg
End Sub

When I open that workbook today, I get two pop up messages generated by that coding:
Expire Today-3.JPG : https://imgur.com/SB35uJU
Expire Today-3.JPG

Today s Expire.JPG : https://imgur.com/hZBXZ2o
Today s Expire.JPG

This was my test data:

Row\Col	A	B	C	D
1	expiry date	mark Brand	value
2	27.06.2019	a	1
3	26.06.2019	b	2
4	25.06.2019	c	3
5	24.06.2019	d	4
6	23.06.2019	e	5
7	22.06.2019	f	6
8	21.06.2019	g	7
9	20.06.2019	h	8
10	27.06.2019	i	9
11	26.06.2019	j	10
12

Au revoir, J'espère que cela vous aidera.
Alan