Macro Iban Bic controleren - verticaal zoeken

[Linda927]

Goedemorgen,

Ik wil graag een macro die controleert of het opgegeven BICnummer (Kolom B) correspondeert met het opgegeven IBAN nummer (Kolom A). Ik heb hierbij de volgende vragen:

Er moet een melding komen wanneer het BICnummer niet overeenkomt met het IBAN nummer.
Bijv 'Fout BIC nummer in cel B6, B7 en B8'
Wanneer het wel overeenkomt hoeft er niets te gebeuren.

Ik heb een voorbeeldbestandje bijgevoegd. In Blad 2 staan de BIC nummers met daarnaast de letters die het rekeningnummer moet bevatten.

Daarnaast zou ik graag willen dat de macro het controleert of de invoer correct is:
IBAN AA00AAAA0000000000
(ook alleen hoofdletters toestaan)

Heeft iemand een idee voor de code?

Groet Linda

 

[gast0660]

Hoi Linda,
In bijlage een vbtje om Iban te checken in excel en een meer elegante oplossing in access
Hopelijk kan je daar al iets mee?

Greetz

 

[Linda927]

Hoi gast0660,

Bedankt voor je snelle reactie. Dit is wel gemakkelijk, maar het gaat in mijn geval om een lijst van 1 tot 10000 regels waar kolom A en B al ingevuld zijn. De macro is echt bedoeld als controle. Er zal een hele lage foutmarge zijn.

Dit is niet helemaal wat ik zoek denk ik , toch bedankt!

Groet Linda

 

[Cheetahke]

Hoi Linda,

Ik kan je wel helpen met de code, maar eerst enkele vraagjes...
Wanneer moet die controle gebeuren? Je hebt hiervoor verschillende mogelijkheden, bvb door het klikken op een knop, of had je iets anders in gedachten?

Staan alle BIC nummers op blad 2, of kunnen er nog bijkomen?

De controle bij invoer is zeker mogelijk, geen probleem.

Ik zal vanavond een voorbeeldje maken en hier posten.

Groetjes,

Cheetahke

Bekijk bijlage Vraag Linda Opgelost.xlsm

 

[Linda927]

Hoi Cheetahke,

Super! Bedankt voor de genomen moeite!

Ik snap niet precies hoe alles werkt (alleen bepaalde gedeelten), dus ik kan het zelf nog niet in mijn andere macro integreren. Mag ik daarom nog wat vragen stellen?
Deze macro start als er op een knop geklikt wordt. Alle BIC nummers staan in Blad 2. Dit staat voorlopig vast.

Er hoeft bij de invoer niets gecontroleerd te worden. Ik gebruik een template waar ik alle regels in plak. Als ik op de knop druk dan gaat de macro lopen.

Het gaat om een bestand tot 10000 regels, maar de foutmarge zal heel klein zijn. Daarom heeft het de voorkeur dat er een MsgBox komt als er een afwijking is gevonden en dat er niet in een andere cel een rode tekst komt die daarna handmatig gecontroleerd moet worden.
(Het zou wel een mogelijkheid zijn dat er een foutmelding in kolom W komt (bij de gevonden Cel), en dat er een MsgBox komt als er een tekst in kolom W staat.)

Daarnaast is er nog een andere variabele waar ik rekening mee moet houden. De waarde in Kolom X is hiervoor bepalend. Wanneer dit bijv. A is dan gelden de IBAN BIC voorwaarden, maar als in Kolom X bijv. B staat dan zou er een Creditcard nummer (16 cijfers) in Kolom AA moeten staan.

Ik ben zo vrij geweest nog een voorbeeldbestandje toe te voegen. Zou je nog tijd willen vrijmaken hier even naar te kijken?

Groetjes Linda

 

[Cheetahke]

Hoi Linda,

Ik zal er eens naar kijken, en proberen om nog meer commentaar toe te voegen in de code, zodat je de werking ervan beter begrijpt.

Groetjes,

 

[Cheetahke]

Hoi Linda,

Ik heb naar je bestandje gekeken, en de code aangepast, maar er treed nu een probleem op bij het controleren van het creditkaartnummer. Dit nummer is waarschijnlijk ingevoerd als getal, maar Excel kan blijkbaar niet werken met getallen van 16 cijfers. Ik ben dit gaan opzoeken via Google, en dit heb ik gevonden...

Wanneer u in een werkbladcel een getal typt dat uit meer dan 15 cijfers bestaat, worden in Microsoft Office Excel cijfers vanaf de zestiende positie gewijzigd in nullen. Bovendien wordt het getal weergegeven in een exponentiële notatie, waarbij een deel van het getal wordt vervangen door E+n, waarbij E (wat exponent betekent) zorgt dat het voorgaande grondtal wordt vermenigvuldigd met 10 tot de macht n.

Als u een aangepaste getalnotatie maakt voor een 16-cijferig creditcardnummer (bijvoorbeeld ################ of ####-####-####-####), zal het laatste cijfer toch in een nul worden veranderd. Als u een 16-cijferig creditcardnummer volledig wilt weergeven, moet u het nummer als tekst opmaken.

Hiervoor heb ik dus geen oplossing om op foutieve ingave te laten controleren.

 

[Linda927]

Goedemorgen Cheetakhe,

Dat is dan niet anders! Wellicht dat er een melding kan komen dat wanneer er een letter gevonden wordt in die cel. Daar zal ik dan nog even naar zoeken

Ben heel benieuwd naar de oplossing die je hebt gevonden en hoe het werkt!

Groetjes Linda

 

[Cheetahke]

Hoi Linda,

Mijn excuses voor het lange wachten voordat ik het bestandje hier gezet heb. Zoals gezegd, het werkt, behalve voor de getallen. Momenteel komen die nog wel bij in de berichtgeving van de gevonden fouten in dit bestandje.
Zoals je kunt zien heb ik verder gewerkt op het eerste bestandje, maar met de gegevens van het 2de bestandje. Dit was gemakkelijker voor mij omdat daar de code al gedeeltelijk instond.

Groetjes,

Cheetahke



Bekijk bijlage Vraag Linda2 Opgelost.xlsm

 

[snb]

controle op 16 cijferig getal (dat altijd als tekst moet worden ingevoerd)

if Len(FormatNumber(Val("1234567890123456"), 0, 0, 0, 0))=16 then msgbox "prima"

 

[Cheetahke]

@snb

controle op 16 cijferig getal (dat altijd als tekst moet worden ingevoerd)

Dat is juist het probleem, snb, deze gegevens zijn niet als tekst ingevoerd, anders was het geen probleem!

 

[snb]

Is toch ook geen probleem:

msgbox Len(FormatNumber(1.23456789012346E+15, 0, 0, 0, 0))

 

[Linda927]

Hoi Cheetahke,

Het deel voor de IBAN en de meldingen werken fantastisch. Door je duidelijke uitleg snap ik nu ook precies wat daar gebeurt en heb ik het kunnen implementeren.

Ik probeer nu bij Case "B" de code van @snb in te voeren maar dat lukt niet.

                Case "B"
                    'controleer of het ingevulde gegeven een getal is of niet
                    If IsNumeric(rngCel.Offset(0, 3)) Then
                        'Als het gegeven numeriek is, bekijk het aantal cijfers
                        'We halen eerst eventuele spaties voor- of achteraan eruit
                        intLengte = Len(Trim(CStr(rngCel.Offset(0, 3).Value)))
                        'Met een select case gaan we weer bepalen wat er moet gebeuren
                        Select Case intLengte
                            Case Is < 16
                                'Als de lengte van het getal kleiner is dan 16 cijfers, wordt een foutmelding gemaakt
                                If strFout = "" Then
                                    'Het is een eerste foutmelding
                                    strFout = "Het getal is te kort in cel " & rngCel.Offset(0, 3).Address
                                Else
                                    'Er is reeds een foutmelding opgeslagen, dus deze wordt een volgende foutmelding.
                                    strFout = strFout & vbNewLine & "Het getal is te kort in cel " & rngCel.Offset(0, 3).Address
                                End If
                            Case Is > 16
                                'Als de lengte van het getal groter is dan 16 cijfers, wordt een foutmelding gemaakt.
                                If strFout = "" Then
                                    'Het is een eerste foutmelding
                                    strFout = "Het getal is te lang in cel " & rngCel.Offset(0, 3).Address
                                Else
                                    'Er is reeds een foutmelding opgeslagen, dus deze wordt een volgende foutmelding.
                                    strFout = strFout & vbNewLine & "Het getal is te lang in cel " & rngCel.Offset(0, 3).Address
                                End If
                            Case Else
                                'Het getal heeft de juiste lengte.
                                'Indien nodig kan er een bijkomende controle op het getal uitgevoerd worden
                                
                        End Select
                    Else
                        'Het is geen numeriek gegeven, dus wordt een foutmelding gemaakt
                        If strFout = "" Then
                            'Het is een eerste foutmelding
                            strFout = "IBAN ingevuld bij A = B in cel " & rngCel.Offset(0, 3).Address
                        Else
                            'Er is reeds een foutmelding opgeslagen, dus deze wordt een volgende foutmelding.
                            strFout = strFout & vbNewLine & "IBAN ingevuld bij A = B in cel " & rngCel.Offset(0, 3).Address
                        End If
                    End If

Enig idee hoe dat hierin verwerkt kan worden?

Groetjes Linda

 

[Warme bakkertje]

intLengte = Len(FormatNumber(rngCel.Offset(0, 3).Value, 0, 0, 0, 0))

 

[Cheetahke]

Hoi Linda,

Ik heb de code van snb kunnen invoegen, en nu geeft die wel de exacte lengte van het getal weer, maar... er kan zich dan een ander probleem stellen.
Ik weet niet of je met die creditcardnummers nog iets anders wil doen dan enkel die controle?

Om de code van snb te gebruiken vervang je deze code

 intLengte = Len(Trim(CStr(rngCel.Offset(0, 3).Value)))

door deze code

intLengte = Len(FormatNumber(1.23456789012346E+15, 0, 0, 0, 0))
of intLengte = Len(FormatNumber(rngCel.Offset(0, 3).Value, 0, 0, 0, 0))

bij Case "B".
Zowel de code van snb, als die van Rudi, geeft dan inderdaad het juiste aantal cijfers weer (16), en je krijgt hierop geen melding meer in je msgbox.

Zoals Rudi hieronder opmerkt, is het misschien aangewezen om zijn code te gebruiken

Groetjes

 

[Warme bakkertje]

@ Cheetahke

Ik denk dat je zoals jij het schrijft altijd 16 gaat krijgen. Kijk maar eens naar post #14.

 

[snb]

Zo:

Sub Knop1_Klikken()
    sn = Blad1.Cells(2, 1).CurrentRegion.Resize(, 5)
    sp = [transpose(blad2!A1:A70&"_"&Blad2!B1:B70)]
    
    For j = 1 To UBound(sn)
       c01 = ""
       If sn(j, 1) = "A" Then
          If IsNumeric(sn(j, 4)) Then
            c01 = " is geen IBAN-nummer"
          Else
             y = StrComp(Space(18), Space(Len(sn(j, 4))))
             If y <> 0 Then c01 = " is te " & Choose(y + 2, "lang", "", "kort")
             If y = 0 Then
                If Not IsNumeric(Mid(sn(j, 4), 9)) Then c01 = " tekst in nummerdeel"
                If c01 = "" Then If sn(j, 5) <> Split(Filter(sp, "_" & Mid(sn(j, 4), 5, 4))(0), "_")(0) Then c01 = " Onjuiste BIC"
             End If
          End If
       Else
          If IsNumeric(sn(j, 4)) Then
            If Len(FormatNumber(sn(j, 4), 0, 0, 0, 0)) <> 16 Then c01 = " is een ongeldig kaartnummer"
          Else
            c01 = " is geen kaartnummer"
          End If
       End If
       If c01 <> "" Then c00 = c00 & vbLf & Cells(j + 1, 4).Address(0, 0) & c01
    Next
    
    MsgBox c00
End Sub

 

[Linda927]

Super, iedereen ontzettend bedankt voor de input! Alles werkt naar behoren