Validation NAS canadien sans VBA

Le
Denys
Bonjour à tous,

Existe-t-il une formule pour valider un numéro d'assurance sociale
canadien ? Je connais la version VBA, mais cette fois j'aimerais avoir
une formule sans VBA, autrement dit, si j'entre un numéro en B1, je
mettrais la formule en C1 qui dirait si le no est valide ou pas.

Merci pour votre temps.et Joyeuses Fêtes.

Denys
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses Page 1 / 3
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
michdenis
Le #22953261
Bonjour,

L'idéal est une procédure comme ceci dans le module de la feuille
où l'action se déroule. Tu adaptes Range("C:C") de la procédure
pour l'adresse de ta plage de cellules de ton application.

Ce code permet la saisie du code postal en minuscule et sans
espace après les 3 et 4 nièmes caractères. Si au lieu d'un message et
d'un format de cellule, tu veux utiliser une autre colonne pour
inscrire vrai ou faux, cela peut s'adapter !

'--------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rg As Range
Set Rg = Intersect(Target, Range("C:C"))
Application.EnableEvents = False
If Not Rg Is Nothing Then
For Each c In Rg
If c <> "" Then
c.Value = UCase(Application.Trim(c))
If c.Value Like "[A-Z][0-9][A-Z] [0-9][A-Z][0-9]" Or _
c.Value Like "[A-Z][0-9][A-Z][0-9][A-Z][0-9]" Then
c.Value = Left(c, 3) & " " & Right(c, 3)
c.Interior.ColorIndex = xlNone
c.Font.ColorIndex = xlAutomatic
Else
MsgBox "le format du code postal n'est pas canadien."
c.Interior.ColorIndex = 3
c.Font.ColorIndex = 2
End If
Else
c.Interior.ColorIndex = xlNone
c.Font.ColorIndex = xlAutomatic
End If
Next
End If
Application.EnableEvents = True
End Sub
'--------------------------------------------

MichD
--------------------------------------------


"Denys" a écrit dans le message de groupe de discussion :


Bonjour à tous,

Existe-t-il une formule pour valider un numéro d'assurance sociale
canadien ? Je connais la version VBA, mais cette fois j'aimerais avoir
une formule sans VBA, autrement dit, si j'entre un numéro en B1, je
mettrais la formule en C1 qui dirait si le no est valide ou pas....

Merci pour votre temps....et Joyeuses Fêtes....

Denys
Denys
Le #22953431
Bonjour Denis,

Merci bien mais c'est pour les numéros d'assurance sociale, pas les
codes postaux !! :-)

En fait, je crois que ce serait une formule Mod 10 ou quelque chose
dans le genre...

Bonne journée

Denys
michdenis
Le #22953471
Ok, désolé ;-)


POUR VALIDER UN NAS, PROCÉDER DE LA FAÇON SUIVANTE :
-- INSCRIRE le NAS sur une feuille de papier, comme suit: 044-096-857;
-- COCHER le deuxième, quatrième, sixième et huitième chiffre;
-- INSCRIRE le NAS de nouveau, mais cette fois en multipliant par deux
les chiffres mentionnés, comme suit : 084-0912-8107.

LORSQUE CETTE MULTIPLICATION DONNE UN NOMBRE À DEUX CHIFFRES, ALORS :
-- ADDITIONNER ces deux chiffres pour n'en faire qu'un seul,
-- ET additionner l'ensemble de ces chiffres, soit : 0+8+4+0+9+1+2+8+1+0+7 = 40.

SI LE TOTAL QUI EN RÉSULTE EST UN MULTIPLE DE DIX, LE NAS EST VALIDE.
Donc, le NAS ci-dessus est valide, puisque le total est 40.

Si tu as besoin d'une fonction personnalisée pour faire cela, je serai de retour
en PM...et j'y regarderai de plus près si j'ai le temps.


MichD
--------------------------------------------
"Denys" a écrit dans le message de groupe de discussion :


Bonjour Denis,

Merci bien mais c'est pour les numéros d'assurance sociale, pas les
codes postaux !! :-)

En fait, je crois que ce serait une formule Mod 10 ou quelque chose
dans le genre...

Bonne journée

Denys
isabelle
Le #22953531
bonjour Denys,

en attendant que Denis revienne,

Sub Test_NAS()
x = Application.Substitute(Range("A1"), "-", "")
For i = 1 To Len(x)
Select Case i
Case 2, 4, 6, 8
y = Mid(x, i, 1) * 2
If y >= 10 Then y = CDbl(Mid(y, 1, 1)) + CDbl(Mid(y, 2, 1))
r = r + y
Case Else
r = r + CDbl(Mid(x, i, 1))
End Select
Next
MsgBox r
End Sub

passe de bonne fête,
isabelle

Le 2010-12-23 11:10, michdenis a écrit :
POUR VALIDER UN NAS, PROCÉDER DE LA FAÇON SUIVANTE :
-- INSCRIRE le NAS sur une feuille de papier, comme suit: 044-096-857;
-- COCHER le deuxième, quatrième, sixième et huitième chiffre;
-- INSCRIRE le NAS de nouveau, mais cette fois en multipliant par deux
les chiffres mentionnés, comme suit : 084-0912-8107.

LORSQUE CETTE MULTIPLICATION DONNE UN NOMBRE À DEUX CHIFFRES, ALORS :
-- ADDITIONNER ces deux chiffres pour n'en faire qu'un seul,
-- ET additionner l'ensemble de ces chiffres, soit : 0+8+4+0+9+1+2+8+1+0+7 = 40.

SI LE TOTAL QUI EN RÉSULTE EST UN MULTIPLE DE DIX, LE NAS EST VALIDE.
Donc, le NAS ci-dessus est valide, puisque le total est 40.

Si tu as besoin d'une fonction personnalisée pour faire cela, je serai de retour
en PM...et j'y regarderai de plus près si j'ai le temps.
isabelle
Le #22953801
pour une formulle perso,

Function nas(cellule As Range) As String
x = Application.Substitute(cellule, "-", "")
For i = 1 To Len(x)
Select Case i
Case 2, 4, 6, 8
y = Mid(x, i, 1) * 2
If y >= 10 Then y = CDbl(Mid(y, 1, 1)) + CDbl(Mid(y, 2, 1))
r = r + y
Case Else
r = r + CDbl(Mid(x, i, 1))
End Select
Next
If r Mod 10 = 0 Then
nas = "VRAI"
Else
nas = "FAUX"
End If
End Function


isabelle

Le 2010-12-23 11:48, isabelle a écrit :
bonjour Denys,

en attendant que Denis revienne,

Sub Test_NAS()
x = Application.Substitute(Range("A1"), "-", "")
For i = 1 To Len(x)
Select Case i
Case 2, 4, 6, 8
y = Mid(x, i, 1) * 2
If y >= 10 Then y = CDbl(Mid(y, 1, 1)) + CDbl(Mid(y, 2, 1))
r = r + y
Case Else
r = r + CDbl(Mid(x, i, 1))
End Select
Next
MsgBox r
End Sub

passe de bonne fête,
isabelle

Le 2010-12-23 11:10, michdenis a écrit :
POUR VALIDER UN NAS, PROCÉDER DE LA FAÇON SUIVANTE :
-- INSCRIRE le NAS sur une feuille de papier, comme suit: 044-096-857;
-- COCHER le deuxième, quatrième, sixième et huitième chiffre;
-- INSCRIRE le NAS de nouveau, mais cette fois en multipliant par deux
les chiffres mentionnés, comme suit : 084-0912-8107.

LORSQUE CETTE MULTIPLICATION DONNE UN NOMBRE À DEUX CHIFFRES, ALORS :
-- ADDITIONNER ces deux chiffres pour n'en faire qu'un seul,
-- ET additionner l'ensemble de ces chiffres, soit :
0+8+4+0+9+1+2+8+1+0+7 = 40.

SI LE TOTAL QUI EN RÉSULTE EST UN MULTIPLE DE DIX, LE NAS EST VALIDE.
Donc, le NAS ci-dessus est valide, puisque le total est 40.

Si tu as besoin d'une fonction personnalisée pour faire cela, je serai
de retour
en PM...et j'y regarderai de plus près si j'ai le temps.
Denys
Le #22953791
Salut denis,

Voici où j'en suis....

Supposons le no suivant...en D19 '044096857, faut mettre un
apostrophe devant le 0...
en E 19 la formule suivante:
=(MID(D19,1,1)&MID(D19,2,1)*2&MID(D19,3,1)&MID(D19,4,1)*2&MID(D19,5,1)&(M ID(D19,6,1)*2&MID(D19,7,1)&MID(D19,8,1)*2&MID(D19,9,1)))
en F 19 la formule suivante:
=MID(E19,1,1)+MID(E19,2,1)+MID(E19,3,1)+MID(E19,4,1)+MID(E19,5,1)+MID(E19 ,6,1)+MID(E19,7,1)+MID(E19,8,1)+MID(E19,9,1)+MID(E19,10,1)+MID(E19,11,1)

La réponse est 40.....

Je ne suis pas certain qu'elle est infaillible cependant, car il n'y
aura pas nécessairement toujours 11 chiffres en E19

Denys

Isabelle, merci pour les bons voeux.....j'essaie de voir sans VBA....
michdenis
Le #22954191
Si tu formates ta colonne au format Texte avant d'y copier
tes NAS, tu n'auras pas de problème avec les 0

Dans un module standard, copie cette fonction personnalisée.

Et dans une cellule à côté de ladite colonne contenant des NAS
tu insères la formule =EstNas(A1) en supposant que A1 contient
un NAS. La fonction retourne Vrai ou Faux selon le cas.

Tu peux tester la fonction plus à fond...mais ça l'air de fonctionner !

'--------------------------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer

N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'Vérification si le résultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'--------------------------------------------------


MichD
--------------------------------------------
"Denys" a écrit dans le message de groupe de discussion :


Salut denis,

Voici où j'en suis....

Supposons le no suivant...en D19 '044096857, faut mettre un
apostrophe devant le 0...
en E 19 la formule suivante:
=(MID(D19,1,1)&MID(D19,2,1)*2&MID(D19,3,1)&MID(D19,4,1)*2&MID(D19,5,1)&(MID(D19,6,1)*2&MID(D19,7,1)&MID(D19,8,1)*2&MID(D19,9,1)))
en F 19 la formule suivante:
=MID(E19,1,1)+MID(E19,2,1)+MID(E19,3,1)+MID(E19,4,1)+MID(E19,5,1)+MID(E19,6,1)+MID(E19,7,1)+MID(E19,8,1)+MID(E19,9,1)+MID(E19,10,1)+MID(E19,11,1)

La réponse est 40.....

Je ne suis pas certain qu'elle est infaillible cependant, car il n'y
aura pas nécessairement toujours 11 chiffres en E19

Denys

Isabelle, merci pour les bons voeux.....j'essaie de voir sans VBA....
michdenis
Le #22954251
Cette fonction est plus complète :

'-----------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer

If IsEmpty(Rg) Then EstNas = False: Exit Function
N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
If Not IsNumeric(N) Then EstNas = False: Exit Function
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'Vérification si le résultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'-----------------------------------



MichD
--------------------------------------------
"michdenis" a écrit dans le message de groupe de discussion : if09ju$mug$

Si tu formates ta colonne au format Texte avant d'y copier
tes NAS, tu n'auras pas de problème avec les 0

Dans un module standard, copie cette fonction personnalisée.

Et dans une cellule à côté de ladite colonne contenant des NAS
tu insères la formule =EstNas(A1) en supposant que A1 contient
un NAS. La fonction retourne Vrai ou Faux selon le cas.

Tu peux tester la fonction plus à fond...mais ça l'air de fonctionner !

'--------------------------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer

N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'Vérification si le résultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'--------------------------------------------------


MichD
--------------------------------------------
"Denys" a écrit dans le message de groupe de discussion :


Salut denis,

Voici où j'en suis....

Supposons le no suivant...en D19 '044096857, faut mettre un
apostrophe devant le 0...
en E 19 la formule suivante:
=(MID(D19,1,1)&MID(D19,2,1)*2&MID(D19,3,1)&MID(D19,4,1)*2&MID(D19,5,1)&(MID(D19,6,1)*2&MID(D19,7,1)&MID(D19,8,1)*2&MID(D19,9,1)))
en F 19 la formule suivante:
=MID(E19,1,1)+MID(E19,2,1)+MID(E19,3,1)+MID(E19,4,1)+MID(E19,5,1)+MID(E19,6,1)+MID(E19,7,1)+MID(E19,8,1)+MID(E19,9,1)+MID(E19,10,1)+MID(E19,11,1)

La réponse est 40.....

Je ne suis pas certain qu'elle est infaillible cependant, car il n'y
aura pas nécessairement toujours 11 chiffres en E19

Denys

Isabelle, merci pour les bons voeux.....j'essaie de voir sans VBA....
michdenis
Le #22954241
Une dernière modification :

'---------------------------------------
Function EstNas(Rg As Range) As Boolean
Dim N As String, A As Integer, V As Variant
Dim T As String, S As Integer

'Si la cellule est vide...
If IsEmpty(Rg) Then EstNas = False: Exit Function
N = Rg
'Supprime le symbole "-" dans le NAS
N = Replace(N, "-", "")
'Supprime tout espace (chr(32) dans le NAS
N = Replace(N, Chr(32), "")
'si la chaîne de caractères n'est pas numérique
If Not IsNumeric(N) Then EstNas = False: Exit Function
'si la chaîne de caractères est différente de 9
If Len(N) <> 9 Then EstNas = False: Exit Function
For A = 1 To Len(N)
If A Mod 2 <> 0 Then
S = S + CInt(Mid(N, A, 1))
Else
T = (Mid(N, A, 1)) * 2
If Len(T) > 1 Then
S = S + CInt((Left(T, 1)) + CInt(Right(T, 1)))
Else
S = S + T
End If
End If
Next
'Vérification si le résultat est un multiple de 10
If Application.Round(S / 10, 5) - Application.Round(S / 10, 0) = 0 Then
EstNas = True
End If
End Function
'---------------------------------------

MichD
--------------------------------------------
Denys
Le #22954331
Merci Denis,

Y a vraiment pas moyen sans VBA ???

N'empêche que je vais garder cette formule même si j'avais autre
chose...

Merci beaucoup pour ton temps....

Denys
Publicité
Poster une réponse
Anonyme