XL 2019 Associer une ville à une région

[JulienJ]

Bonjour,
J'ai une colonne qui ne contient que des noms de villes. J'essai de créer une fonction qui me retourne un numéro associé à la région à laquelle appartient la ville sélectionnée dans la fonction.
J'ai créer la fonction suivante pour mais elle ne fonctionne pas, #VALEUR! est affiché en résultat.

Function Region(city)

    If Not Intersect(city, Sheet2.Range("U3:U15")) Is Nothing Then
        Region = 2
    End If
    If Not Intersect(city, Sheet2.Range("X3:X15")) Is Nothing Then
        Region = 3
    End If
    If Not Intersect(city, Sheet2.Range("AA3:AA15")) Is Nothing Then
        Region = 4
    End If
    If Not Intersect(city, Sheet2.Range("AD3:AD15")) Is Nothing Then
        Region = 5
    End If
    If Not Intersect(city, Sheet2.Range("AG3:AG15")) Is Nothing Then
        Region = 6
    End If
    If Not Intersect(city, Sheet2.Range("AJ3:AJ15")) Is Nothing Then
        Region = 7
    End If
End Function

Dans les Sheet2.Range("U3:U15") etc... sont contenues le nom des villes situées dans la région.
Si vous trouvez les erreurs, je suis preneur.

Merci

 

[mapomme]

Bonjour @JulienJ ,

Il me semble :

• que vous passez une cellule en argument de la fonction
• que vous recherchez l'intersection des plages avec cette cellule

Qu'est ce que l'intersection de deux plages ?
C'est l'ensemble des cellules qui sont à la fois dans la plage1 et dans la plage2

exemple :

• Intersect( range("a1:b4"), range("b2:d3")) seront les cellules B2 et B3 : range("b2:b3")
• Intersect( range("a1:b4"), range("a10:b13")) renverra le range Nothing (les deux plages n'ont pas d'élément communs)
• Intersect( range("a1:a10"), range("a2")) renverra la cellule A2 : range("a2")

Vous constatez qu'on ne considère que l’adresse physique des cellules pour déterminer si elles font partie ou non de l'intersection. On ne prend jamais en compte la valeur des cellules.

Or vous, ce que vous cherchez à savoir, c'est si la valeur d'une cellule est égale à une des valeurs de la plage. C'est donc différent de l'intersection "physique".

D'ailleurs dans votre cas, il est fort probable que la cellule passée en argument ne fasse jamais partie des plages à examiner. Donc l’intersection sera toujours vide et on aura toujours une erreur en sortie de la fonction.

Il faut donc comparer la valeur de la cellule à chaque valeur des plages. il y a des méthodes assez rapides.

 

[sylvanu]

Bonjour Julien, MaPomme,
Un essai en PJ.
On peut simplifier, mais le principe est là et fonctionne.

Function Region(city)
Region = "" ' cas où la ville n'est pas référencée
With Sheets("Sheet2")
    If Application.CountIf(.Range("U3:U15"), city) > 0 Then Region = 2
    If Application.CountIf(.Range("X3:X15"), city) > 0 Then Region = 3
    If Application.CountIf(.Range("AA3:AA15"), city) > 0 Then Region = 4
    If Application.CountIf(.Range("AD3:AD15"), city) > 0 Then Region = 5
    If Application.CountIf(.Range("AG3:AG15"), city) > 0 Then Region = 6
    If Application.CountIf(.Range("AJ3:AJ15"), city) > 0 Then Region = 7
End With
End Function

 

[mapomme]

Essayez cette fonction:

Function Region(city)
Dim i
Region = 0
For i = 0 To 5
   If Application.IfError(Application.Match(city, Feuil5.Range("U3:U15").Offset(0, 3 * i), 0), 0) > 0 Then Region = i + 2: Exit Function
Next i
End Function

edit : bonjour @sylvanu