XL 2016 Mettre en application INDEX et SOMMEPROD en BVA

[candido57]

Bonjour,

J'ai une formule qui est très longue .
Je voudrais m'en servir en BVA, mais je connais pas le code. Pouvez vous me mettre le code
La formule ci-dessous elle va chercher le dernier résultat de la liste > Date saisi en C2.
J'ai dû mettre esterreur car sans ça j'ai une #VALEUR!
J'ai dû faire cette formule car sur le PC au boulot , je n'ai pas RECHERCHEX version Excel trop ancienne
Est ce qu'il y a une formule plus courte ?

Merci beaucoup pour votre aide

=@SI(ESTERREUR(@SI($H$1>=24;"Pas de couche";INDEX(t_Base[Date];SOMMEPROD((t_Base[N° de carte + Prénom]=$C$2)*("T4"=t_Base[Taille])*LIGNE(t_Base[N° de carte + Prénom]))-6)));"";SI($H$1>=24;"Pas de couche";INDEX(t_Base[Date];SOMMEPROD((t_Base[N° de carte + Prénom]=$C$2)*("T4"=t_Base[Taille])*LIGNE(t_Base[N° de carte + Prénom]))-6)))

 

[sousou]

Bonjour à tous
Regarde ceci fonction derdate(valeur;carte)
Avec le sourire
😌

 

[candido57]

Bonjour à tous
Regarde ceci fonction derdate(valeur;carte)
Avec le sourire
😌

Bonsoir,

Serait-il possible m'expliquer ligne par ligne ?

Function derdate(valeur, carte)

Set tableau = Sheets("base").ListObjects("T_base")
For Each i In tableau.ListColumns(7).DataBodyRange

'MsgBox i.Offset(0, -4) & " " & i & " " & i.Offset(0, -5)
If UCase(i) = valeur And i.Offset(0, -4) = carte Then
If CDate(i.Offset(0, -5)) > madate Then madate = CDate(i.Offset(0, -5))
End If

Next

derdate = madate
If derdate = 0 Then derdate = ""
End Function


Ca fonctionne pour le lait , mais pas pour les couches, il faudrait prendre la colonne 11.
Colonne 7 pour le lait
Colonne 11 pour les couches car plusieurs référence en couche.


En tout cas , merci beaucoup

Bonne soirée

 

[sousou]

Re
en fonction de ta remarque, j'ai modifié le code et les arguments de la fonction afin de répondre au faite que la recherche ne se fait pas sur les même colonne ( plus quelques explications)
derdate(valeur,carte, colonne)
J'ai ajouté un trim et , pour m' exempter des espaces avant et après, mais fait attention essais d'avoir des texte identiques

 

[candido57]

Re
en fonction de ta remarque, j'ai modifié le code et les arguments de la fonction afin de répondre au faite que la recherche ne se fait pas sur les même colonne ( plus quelques explications)
derdate(valeur,carte, colonne)
J'ai ajouté un trim et , pour m' exempter des espaces avant et après, mais fait attention essais d'avoir des texte identiques

Bonjour,
Je regarderai demain et je reviens vers vous.
Merci beaucoup pour votre aide
Bonne journée

 

[sousou]

Bonjour
J'ai simplifié la fonction en supprimant un argument.
La carte est maintenant un champs nommé (carte)
J'ai aussi modifié la détection des colonnes avec non plus le numéro, mais le titre de la colonne du tableau.
L'avantage de cette version, est que si la forme du tableau change la fonction fonctionne toujours
et si tu déplace le numéro de carte , Idem.

 

[candido57]

Bonjour
J'ai simplifié la fonction en supprimant un argument.
La carte est maintenant un champs nommé (carte)
J'ai aussi modifié la détection des colonnes avec non plus le numéro, mais le titre de la colonne du tableau.
L'avantage de cette version, est que si la forme du tableau change la fonction fonctionne toujours
et si tu déplace le numéro de carte , Idem.

Bonjour,
Je suis sur mon fichier ça fonctionne bien, mais le résultat ne s'affiche pas automatiquement lors de la saisi du n° de carte. J'ai regardé mes paramètres , je suis bien en calcul en auto.
j'ai essayer de rajouter à la fin de la macro > Application.Calculation = xlAutomatic
mais ça fonctionne pas, je suis obligé de me mettre sur la cellule est faire enter.
En théorie, si je saisi cellule Carte, les dates doivent changer sur les tableau jaune et bleu suivant le numéro
J'ai aussi essayé ci-dessous mais ça fonctionne pas , je pense que la macro que vous avez faire est une FUNCTION et ça fonctionne seulement avec SUB

Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E2")) Is Nothing Then

derdate

End If

End Sub

 

[sousou]

Bonjour
ajoute ceci en débute de fonction
application.volatile

 

[candido57]

Bonjour
ajoute ceci en débute de fonction
application.volatile

Bonsoir,

Merci beaucoup ça fonctionne

Bon week end

 

[candido57]

Bonsoir,

Merci beaucoup ça fonctionne

Bon week end

Bonjour,
Encore un petit coup de main SVP
Sur le même fichier , la feuille Articles, en K9 puis les autres cellules en jaunes.
Faire le même procédé qu'avec la date.
Si je vois le N° de carte colonne C "Base", Taille couche colonne K "Base" faire apparaitre 2 en K9 "Articles", suivant les couches si la quantité prise est 2 sinon rien.
Je ne sais pas si on peut du même style de derdate. J'ai fait derligne, peux tu regarder s'il y a quelque chose à modifier

Merci beaucoup

 

[sousou]

Tu n'étais pas très loin,
modifie comme ceci

Function derligne(valeur, article)

Application.Volatile

'initialisation
carte = ThisWorkbook.Names("Carte").RefersToRange.Value 'numéro de carte trouvé dans les celule nommées
Set tableau = Sheets("base").ListObjects("T_base") 'Définit le tableau
colquant = tableau.ListColumns("Quantité prise").DataBodyRange.Column ' numéro de la colopnne ou trouver la date
colcarte = tableau.ListColumns("N° de carte").DataBodyRange.Column ' numéro de la colopnne ou trouver le numéro de carte


For Each i In tableau.ListColumns(article).DataBodyRange 'pour chaque lignes de la colonne du tableau

'MsgBox i.Parent.Cells(i.Row, colcarte) & "/" & i & "/" & i.Parent.Cells(i.Row, colquant)
'trim supprimme les espaces avant et après
'ucase convertit tout en majuscule
'si i = la valeur et que le numéro de carte correspond

If Trim(UCase(i)) = valeur And i.Parent.Cells(i.Row, colcarte) = carte And i.Parent.Cells(i.Row, colquant) = 2 Then
derligne = 2

End If

Next

If derligne = 0 Then derligne = ""
End Function

 

[candido57]

Tu n'étais pas très loin,
modifie comme ceci

Function derligne(valeur, article)

Application.Volatile

'initialisation
carte = ThisWorkbook.Names("Carte").RefersToRange.Value 'numéro de carte trouvé dans les celule nommées
Set tableau = Sheets("base").ListObjects("T_base") 'Définit le tableau
colquant = tableau.ListColumns("Quantité prise").DataBodyRange.Column ' numéro de la colopnne ou trouver la date
colcarte = tableau.ListColumns("N° de carte").DataBodyRange.Column ' numéro de la colopnne ou trouver le numéro de carte


For Each i In tableau.ListColumns(article).DataBodyRange 'pour chaque lignes de la colonne du tableau

'MsgBox i.Parent.Cells(i.Row, colcarte) & "/" & i & "/" & i.Parent.Cells(i.Row, colquant)
'trim supprimme les espaces avant et après
'ucase convertit tout en majuscule
'si i = la valeur et que le numéro de carte correspond

If Trim(UCase(i)) = valeur And i.Parent.Cells(i.Row, colcarte) = carte And i.Parent.Cells(i.Row, colquant) = 2 Then
derligne = 2

End If

Next

If derligne = 0 Then derligne = ""
End Function

Impeccable, super, merci beaucoup, c'est exactement ça
Bon week end

 

[candido57]

Impeccable, super, merci beaucoup, c'est exactement ça
Bon week end

Bonjour,
Une petite question. Lorsque j'ouvre mon UserForm, je choisi un n° de carte et si l'enfant plus de 24 mois, j'aimerais avoir une alerte (MsgBox) , est ce possible ? Pas droit au couche par exemple.
et aussi, Si >=18 mois et <24 mois , a droit 1X par mois
Feuille "Articles" en H1 il y a les mois > voir fichier dans dialogue précédent.
MsgBox doit s'afficher au moment saisi en feuille Base. Dès que choisi le n° de carte.

Merci

 

[candido57]

Tu n'étais pas très loin,
modifie comme ceci

Function derligne(valeur, article)

Application.Volatile

'initialisation
carte = ThisWorkbook.Names("Carte").RefersToRange.Value 'numéro de carte trouvé dans les celule nommées
Set tableau = Sheets("base").ListObjects("T_base") 'Définit le tableau
colquant = tableau.ListColumns("Quantité prise").DataBodyRange.Column ' numéro de la colopnne ou trouver la date
colcarte = tableau.ListColumns("N° de carte").DataBodyRange.Column ' numéro de la colopnne ou trouver le numéro de carte


For Each i In tableau.ListColumns(article).DataBodyRange 'pour chaque lignes de la colonne du tableau

'MsgBox i.Parent.Cells(i.Row, colcarte) & "/" & i & "/" & i.Parent.Cells(i.Row, colquant)
'trim supprimme les espaces avant et après
'ucase convertit tout en majuscule
'si i = la valeur et que le numéro de carte correspond

If Trim(UCase(i)) = valeur And i.Parent.Cells(i.Row, colcarte) = carte And i.Parent.Cells(i.Row, colquant) = 2 Then
derligne = 2

End If

Next

If derligne = 0 Then derligne = ""
End Function

Bonsoir Sousou,
J'ai fait tourner mon fichier avec ta macro Derligne qui fonctionne, mais lorsque je saisi la même n° de carte et il prend 1 articles au lieu de 2 , la macro va cherche le dernier 2 dans quantité et moi je souhaite vraiment la dernière cellule saisi. Donc si c'est 1 , il faut qu'il renvoie 1.

Et pour la macro derdate, j'ai essayé de changer la formule =derligne=(B33;"Article") au lieu de "Taille", mais ça fonctione pas

 

[sousou]

Je regarde demain

 

[candido57]

Je regarde demain

Ok merci beaucoup
Bonne soirée