Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Hyperlink in VBA

    Hi all,

    I would like to ask another question, as even though I have the power of google at my fingertips, I always end up getting my answers from here instead.

    I would like to build a hyperlink in VBA consisting of cell contents.

    I also want to strip spaces from the cell content using a function the same as excel's =SUBSTITUTE(a1," ","") function.

    It will also be long, ie, consisting of upwards of 60 cell contents

    I also need it to run from a button click, NOT paste the formula to a cell due to its length.

    So, to summarise with an example.

    I have in cells a7:a60 UK postcodes, that follow the format AB11 1CD (LLNN NLL).
    I want to include these in a formula related to Google Maps, an example being =hyperlink(concatenate("http://www.maps.google.com.html?=",SUBSTITUTE(B7," ",""),"&l=",SUBSTITUTE(B8," ",""),etc.
    I created this link in a cell but it stops at B29 due to the length of the resulting hyperlink.

    (I know the above "www...." address might not work, I included it for demo purposes)

    I hope all the above is clear enough guys, get back to me for any further info

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi

    The vba Trim() function might work for you in place of the substitute expression.
    Put something in A1 that contains spaces fore and aft then run this.
    Code:
    Sub testTrim()
        Dim sNew As String
        sNew = Trim(Range("a1"))
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = sNew
    End Sub
    G

  3. The Following User Says Thank You to geofrichardson For This Useful Post:

    leeroysilk (2015-07-30)

  4. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Sample to generaste URL

    Hi

    Here is a simple example
    Code:
    Sub makeURL()
    Dim sURL As String
        sURL = Range("a2")
        sURL = sURL & Range("a3")
        sURL = sURL & Range("a4")
        sURL = sURL & Range("a5")
        sURL = sURL & Range("a6")
        sURL = sURL & Range("a7")
        sURL = sURL & Range("a8")
        
        Range("a10").Value = sURL
        Range("a11").Select
        With ActiveSheet
            .Hyperlinks.Add anchor:=.Range("A11"), _
            Address:=sURL
        End With
    
    End Sub
    Screen shot of associated spreadsheet.
    makeURL.png

    Obviously you need to work on a loop to drag in 60 cells and trim each up a bit.
    Hope it helps

    Cheers
    G

  5. The Following User Says Thank You to geofrichardson For This Useful Post:

    leeroysilk (2015-07-30)

  6. #4
    Lounger
    Join Date
    Aug 2014
    Posts
    31
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Geof,

    Thanks very much, thanks have been added, however, I fixed my issue avoiding VBA by doing one simple thing.....removing the function HYPERLINK from the formula. I thought it was CONCATENATE that was causing my issue, but it was the HYPERLINK function that has the 255 character limit, so by removing that, and leaving the "http://...." part in my formula, I am now resolved.

    Cheers pal

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Sub testTrim()
    Dim sNew As String
    sNew = Trim(Range("a1"))
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = sNew
    End Sub
    Geof,

    Keeping in mind that the Trim function removes only leading and trailing spaces, to save a few lines of code, you could use:
    Code:
    Sub testTrim()
    [a1] = Trim([a1])
    End Sub
    AB11 1CD (LLNN NLL)
    however, because the spaces were mid string, the substitute function would have been the proper choice.

    Maud

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    geofrichardson (2015-08-02)

  9. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi Maud

    Space Mid String
    Thanks for the advice on substitute() v Trim().
    I was thinking that it was necessary to preserve the space mid string in that Post Code.
    I wondered how it might mess with the URL though.
    G

  10. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Geof,

    Since URLs don't have spaces, my guess is that they need to be removed mid string but so should the leading and trailing as you suggest.

    Maud

Posting Permissions

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