HI I have below fantastic code thanks to Marc. Its really a brilliant code. But i need a little change. If validate a IBAN number
Italien number in the home page http://www.ibancalculator.com it gives normally the city bank name. I need this name to be displayed in the third column C2 and down. In column A after below code you paste the Iban in and in column B it give you a true or false. So what i need is the citibank name,.
At the home page it looks like this. if you example validate this number. IT34N0514275820134570000121
it will display this below in the home page. SOLOFRA is the citybank name. So this i need to be in same row but column C.
Result
This is a valid IBAN.
IBAN: IT34N0514275820134570000121
BIC: BCPTITNN
Bank: ""BANCA DI CREDITO POPOLARE", SOCIETA' COOPERATIVA PER AZIONI"
SOLOFRA This number i need
Branch number: 75820
Data valid as of: 21. 5. 2013 (May 21, 2013).
Hope Marc have time to help, but others also if they can.
HI Marc No not really your right about that. It could just be the city bank name in column B for sure. So if you can make it there its also perfect for sure. Thanks for taking your time have a look at this. I just dont know how to make it displayed there. Its over my skills i think. Your code you provided was really wow so good. So if time yes in column B would be perfect.
Function LastVRow&() With ActiveWindow.VisibleRange: LastVRow = .Rows(.Rows.Count).Row - 1: End With End Function
Sub ControlIBANxml() Dim oReq As Object, SP$() Set oReq = CreateObject("MSXML2.XMLHTTP") Me.Activate: D& = LastVRow: S& = D: ActiveWindow.ScrollColumn = 1 L& = Cells(1).CurrentRegion.Rows.Count For R& = 2 To L If Cells(R, 2).Value = "" Then With ActiveWindow If R < .VisibleRange(1).Row Then .ScrollRow = R: D = LastVRow: S = D _ Else If R > D Then .ScrollRow = R - 1: D = LastVRow End With
With oReq .Open "POST", "http://www.ibancalculator.com/iban_validieren.html", False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .setRequestHeader "DNT", "1" On Error Resume Next .send "tx_valIBAN_pi1%5Biban%5D=" & Cells(R, 1).Value & _ "&tx_valIBAN_pi1%5Bfi%5D=fi&no_cache=1&Action=validate+IBAN" On Error GoTo 0
If .Status = 200 Then V = InStr(.responseText, "This is a valid IBAN.") > 0 If V Then P& = InStr(.responseText, "</p><p><b>Branch number:</b>") If P Then B& = InStrRev(.responseText, ">", P - 1) + 1 If P > B Then SP = Split(Mid$(.responseText, B, P - B), vbLf) V = SP(UBound(SP)) End If End If End If
Cells(R, 2).Value = V Else Cells(R, 2).Value = .Status & " : " & .statusText: Beep: Exit For End If End With End If Next
Set oReq = Nothing If D > S And R > L Then Application.Wait Now + 0.00001: ActiveWindow.ScrollRow = 1 End Sub
Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
Last edited by Marc L; 12-15-2014 at 11:26 AM.
Reason: optimization …
Hi Marc. Wow its really just working fantastic. I speakless hehe. Fantastic and the speed is amazing. Thanks allot really your a master with these rip from home page really. Wow
Have a great day we are up in 2 bears or more in Madrid hehe.
Bookmarks