XL 2010 [resolu] vba imbriquer concatenate et match

[deps]

Bonjour

Dans mon fichier je n'arrive pas à réaliser la macro vba qui permet d'imbriquer la fonction concatener et index/match (colonne B) alors que sans vba (colonen C ) elle fonctionne.

j'ai tenté plusieurs formules différentes sans résultats positifs.

Un forumeur aurait il une idée ?

merci
deps

 

[Hieu]

Salut,

une idée :

Sub classement()
Dim a&
      ' supprime le classment EN COLONNE B
       Range("B2:B600").ClearContents
Set WF = WorksheetFunction
Set rang = Sheets("matchs").Range("f:f")
Set code = Sheets("matchs").Range("a:a")
     
      For a = 2 To Range("a65000").End(xlUp).Row ' Pour chaque ligne de N° 2 jusque la dern ligne vide
      'la cellule avec ligne = variable a
      'nom de range "rang" et "code"
      Cells(a, 2).Value = WF.Index(rang, WF.Match(Cells(a, 1) & 1, code, 0))
      Next a
End Sub

la fonction concatene, j'savais même pas qu'elle existait ^^
Tu peux faire a2 & 1, ça marche pareil

 

[deps]

Bonsoir

merci pour ce code

entre temps j'avais vu ma gourde au niveau de "Range("B65000").End(xlUp).Row" au lieu de Range("A65000").End(xlUp).Row

donc il fallait que je mette "WorksheetFunction" ! pour que cela fonctionne. Merci je n'aurais jamais trouvé seul !
Les set permettent d'avoir des lignes plus réduites. Est ce la seule utilité ?

je savais que concatenate pouvais être remplacé par & mais parfois c'est plus lisible l'un ou l'autre.

enfin j'ai mis "Range("B2:B600").ClearContents"
mais je souhaiterais que la fin de la plage (ici B600) soit dynamique c'est à dire du style dernière ligne non vide , un truc avec "End(xlUp).Row"
Aurais tu une idée ?

merci

Deps

 

[deps]

re

A la place de
Cells(a, 2).Value = WF.Index(rang, WF.Match(Cells(a, 1) & 1, code, 0))
j'ai tenté
Cells(a, 2).Value = Application.WorksheetFunction.Index(rang, Application.WorksheetFunction.Match(Cells(a, 1) & 1, code, 0), 0)
et
Cells(a, 2).Value = WorksheetFunction.Index(rang, WorksheetFunction.Match(Cells(a, 1) & 1, code, 0), 0)

mais ça ne fonctionne pas
tant pis je garde la forme raccourcie .

Bonne soirée à tous
Deps

 

[Hieu]

Yep,

Pour les set, perso, j'm'en sers pour éviter la fonction "with", c'est déjà pas mal.

Pour le nettoyage, tu peux le mettre ainsi :

Range(Range("b2"), Range("b65000").End(xlUp)).ClearContents

Cette formule Range("B65000").End(xlUp).Row te donne la ligne, soit une valeur

Tu peux, si tu veux utiliser le .Row, l'écrire comme ça :

Range("B2:B" & Range("B65000").End(xlUp).Row).ClearContents

 

[Hieu]

Si tu veux virer le set, tu peux écrire :

Cells(a, 2).Value = WorksheetFunction.Index(rang, WorksheetFunction.Match(Cells(a, 1) & 1, code, 0), 0)

 

[deps]

Re

merci pour ces infos bien pratiques !

Deps

 

[deps]

Salut

j'ai mis le code ci dessous mais ça bug sur la ligne de la formule

Sub classement2()
Dim ligne&
     
     ' supprime le classment EN COLONNE B
      'Range("B2:B600").ClearContents
       Range("B2:B" & Range("B65000").End(xlUp).Row).ClearContents
      
'Set WF = WorksheetFunction
'Set rang = Sheets("matchs").Range("f:f")
'Set code = Sheets("matchs").Range("a:a")
    
      For ligne = 2 To Range("A65000").End(xlUp).Row ' Pour chaque ligne de N° 2 jusque la dern ligne vide
     'la cellule avec ligne = variable a
     'nom de range "rang" et "code"
     'Cells(ligne, "B").Value = WF.Index(rang, WF.Match(Cells(ligne, "A") & 1, code, 0))
     Cells(a, 2).Value = WorksheetFunction.Index(rang, WorksheetFunction.Match(Cells(a, 1) & 1, code, 0), 0)
      Next ligne
End Sub

 

[Hieu]

Vba ne comprend pas les valeurs "rang" et "code".
Tu peux appeler tes selections ainsi :

Range("rang")

Mais ça ne marche pas lorsque tu es sur plusieurs feuilles, pour ça, que j'avais créé les set "rang" et "code"

Perso, j'utilise jamais les plages nommées, car, lorsque quelqu'un d'autre ouvre la feuille, la premiere chose à faire, est de repérer ces plages.

 

[deps]

Bonsoir

les 2 formules "match" fonctionnent bien si elles trouvent une solution à la recherche

 'Cells(ligne, "B").Value = WF.Index(rang, WF.Match(Cells(ligne, "A") & 1, code, 0))
     Cells(a, 2).Value = WorksheetFunction.Index(rang, WorksheetFunction.Match(Cells(a, 1) & 1, code, 0), 0)

cependant si elles renvoient une erreur de type #n/a ou autre il y a un bug.
je souhaiterais ajouter un si esterreur ou estna (...) then """" else (formule ) ou si non(esttexte() ou si non(estnum())) selon le cas

comment placer cette recherche d'erreur correctement dans le code ?

merci
deps

 

[Hieu]

Salut,

En repartant de mon code, post #2 :

Sub classement()
Dim a&
      Range("B2:B600").ClearContents
Set WF = WorksheetFunction
Set rang = Sheets("matchs").Range("f:f")
Set code = Sheets("matchs").Range("a:a")
On Error Resume Next                                                               ' cette ligne à rajouter
      For a = 2 To Range("a65000").End(xlUp).Row 
     Cells(a, 2).Value = WF.Index(rang, WF.Match(Cells(a, 1) & 1, code, 0))
     If IsEmpty(Cells(a, 2).Value) Then Cells(a, 2).Value = "toto"  ' cette ligne à rajouter
      Next a
End Sub

 

[deps]

Bonsoir

super je vais tester.
puis-je te contacter en mp?

merci
Deps

 

[Hieu]

Salut,
oui, c'est possible.

N'oublie pas de modifier ton post en [Resolu] Titre, si ton probleme est resolu

 

[deps]

Bonsoir

je galère avec les formules vba dans un autre fichier du même type

je souhaitais en cells(ligne,16) calculer le nombre de filles
la formule simple s'écrivant sans macro pour la ligne 6
=si (droite(F6) ="F"; 1;0)+si (droite(H6) ="F"; 1;0)+si (droite(J6) ="F"; 1;0)+si (droite(L6) ="F"; 1;0)

Idem en cells(ligne,17) pour les 'G"
=si (droite(F6) ="G"; 1;0)+si (droite(G6) ="F"; 1;0)+si (droite(G6) ="F"; 1;0)+si (droite(G6) ="F"; 1;0)

j'ai commencé par écrire
If Right(Cells(ligne, 6).Value) = "f" Then Cells(ligne, 16).Value = 1
mais cela ne fonctionne pas

par contre j'ai réussi pour le calcul d'une moyenne d'âge 🙂
'calcul moyenne age en colonne R (18)
Cells(ligne, 18).Value = Application.Average(CDbl(Cells(ligne, "G")), CDbl(Cells(ligne, "I")), CDbl(Cells(ligne, "k")), CDbl(Cells(ligne, "M")))
mais je dois mettre des cnum() c'est à dire cdbl() avant chaque info chiffrée !

merci pour votre aide
Cordialement
Deps

 

[Hieu]

Salut,

La fonction si en vba s'ecrit : IIf
La formule excel :=si (droite(F6) ="G"; 1;0)+si (droite(G6) ="F"; 1;0)+si (droite(G6) ="F"; 1;0)+si (droite(G6) ="F"; 1;0)
donne :

Cells(ligne, 16) = IIf(Right(Cells(ligne, 6)) = "G", 1, 0)+ ...

Pour info,
Plutot que d'utiliser les "cells", tu peux utiliser les range, ce qui te donne plus de clarté (si tu travailles en colonne lettre) :

Range("P" & ligne) = IIf(Right(Range("F" & ligne)) = "G", 1, 0)+ ...

A tester, je n'ai pas de fichier, pour faire de tests.