+ Reply to Thread
Results 1 to 5 of 5

Macro to validate need change

  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Macro to validate need change

    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.

    Thanks in advance Sincerely
    Abjac

    Marcs fantastic code

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro to validate need change


    Hi,

    do you really need city in column C ? Could replace "True" in column B. As you need …

  3. #3
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Macro to validate need change

    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.


    Sincerely
    Abjac

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    PHP Code: 
    Function LastVRow&()
        
    With ActiveWindow.VisibleRange:  LastVRow = .Rows(.Rows.Count).Row 1:  End With
    End 
    Function


    Sub ControlIBANxml()
                
    Dim oReq As ObjectSP$()
                
    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(R2).Value "" Then
                With ActiveWindow
                    
    If < .VisibleRange(1).Row Then .ScrollRow RLastVRowD _
                                  
    Else If D Then .ScrollRow 1LastVRow
                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(R1).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">"1) + 1
                                
    If B Then
                                    SP 
    Split(Mid$(.responseTextBB), vbLf)
                                     
    SP(UBound(SP))
                                
    End If
                            
    End If
                        
    End If

                        
    Cells(R2).Value V
                    
    Else
                        
    Cells(R2).Value = .Status " : " & .statusText:  Beep:  Exit For
                    
    End If
                
    End With
            End 
    If
        
    Next

        Set oReq 
    Nothing
        
    If And L Then Application.Wait Now 0.00001ActiveWindow.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 …

  5. #5
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Macro to validate need change

    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.

    Thanks allot

    Sincerely
    Abjac

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro with condition of Period, before run, macro should validate the date and Path of fil
    By caapoorvajain in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2012, 04:11 AM
  2. [SOLVED] Need method to validate if user has rights to change cell contents
    By stubbsj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-24-2012, 06:05 PM
  3. Macro to validate a condition and change cell color
    By kapil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2010, 10:10 AM
  4. Validate two fields when one field you need a formula to validate
    By cmwilbur in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-29-2010, 11:32 AM
  5. Clear data on change of validate list
    By d0n in forum Excel General
    Replies: 2
    Last Post: 06-05-2009, 06:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1