none
Trying to adjust macro to apply to a fixed range of cells RRS feed

  • Question

  • Hi everyone,

    I am using Excel 2010.  I have the following VBA code:

    Sub Apply_Hyperlinks()
        Dim intCount As Integer
        Dim rngRange As Range
        Dim strTMP As String
        For Each rngRange In Selection
            If Trim(rngRange.Value) <> "" Then
                strTMP = rngRange.Text
                intCount = Application.Find("http://", strTMP, 1)
                rngRange.Hyperlinks.Add Anchor:=Cells(rngRange.Row, rngRange.Column), Address:=Mid(strTMP, intCount), ScreenTip:=Mid(strTMP, intCount), TextToDisplay:=Left(strTMP, intCount - 1)
            End If
        Next rngRange
    End Sub

    I found this code online, and with some modifications, it essentially identifies of a text string contains "http://" and if so, the entire cell is converted to a hyperlink using the http:// address as the destination.  Right now, I have to identify the cells myself and select them individually.

    What I'd like to do is adjust the code so that it will be applied to Range("K1:K3000", "M1:M3000").  I tried simply changing the Selection section to indicate the range, but I get an Run-time error 13, mismatch.  I think its because some of the cells in the range are blank, but I'm not absolutely certain. 

    Any help would be greatly appreciated.

    Thanks!


    Thursday, July 24, 2014 12:05 PM

Answers

  • Try the code below.

    A few notes. Without some examples of what text is being converted I cannot be sure if it will work for you. I am guessing that due to the intCount that http:// may not be the first characters in your string. Is this correct? Because otherwise I cannot see what purpose it serves.

    Sub Apply_Hyperlinks()
        Dim rngToSearch As Range
        Dim rngToFind As Range
        Dim strFirstAddr As String
        Dim intCount As Integer
        Dim strTMP As String
       
        With Worksheets("Sheet1")   'Edit "Sheet1" to your worksheet name
            Set rngToSearch = Union(.Range("K1:K3000"), .Range("M1:M3000"))
        End With
       
        With rngToSearch
            Set rngToFind = .Find(What:="http://", _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                           
            If Not rngToFind Is Nothing Then
                strFirstAddr = rngToFind.Address
                Do
                    strTMP = rngToFind.Text
                    intCount = Application.Find("http://", strTMP, 1)
                    rngToFind.Hyperlinks.Add Anchor:=rngToFind, _
                            Address:=Mid(strTMP, intCount), _
                            ScreenTip:=Mid(strTMP, intCount), _
                            TextToDisplay:=Left(strTMP, intCount - 1)
                           
                    Set rngToFind = .FindNext(rngToFind)
                   
                    If rngToFind Is Nothing Then Exit Do
                   
                Loop While rngToFind.Address <> strFirstAddr
            End If
       
         End With
    End Sub


    Regards, OssieMac

    Friday, July 25, 2014 7:01 AM
  • Hi,

    Based on your code, I find you are using a method "Application.Find". I cannot find such method in Excel Object Model. After testing, if you type in a string "abc" which don't contain "http://", it will throw out such error message "run-time error 13".

    I think OssieMac has provided a correct way for your reference. You should use the Range.Find Method instead of Application.Find method.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 25, 2014 8:36 AM
    Moderator

All replies

  • Try the code below.

    A few notes. Without some examples of what text is being converted I cannot be sure if it will work for you. I am guessing that due to the intCount that http:// may not be the first characters in your string. Is this correct? Because otherwise I cannot see what purpose it serves.

    Sub Apply_Hyperlinks()
        Dim rngToSearch As Range
        Dim rngToFind As Range
        Dim strFirstAddr As String
        Dim intCount As Integer
        Dim strTMP As String
       
        With Worksheets("Sheet1")   'Edit "Sheet1" to your worksheet name
            Set rngToSearch = Union(.Range("K1:K3000"), .Range("M1:M3000"))
        End With
       
        With rngToSearch
            Set rngToFind = .Find(What:="http://", _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                           
            If Not rngToFind Is Nothing Then
                strFirstAddr = rngToFind.Address
                Do
                    strTMP = rngToFind.Text
                    intCount = Application.Find("http://", strTMP, 1)
                    rngToFind.Hyperlinks.Add Anchor:=rngToFind, _
                            Address:=Mid(strTMP, intCount), _
                            ScreenTip:=Mid(strTMP, intCount), _
                            TextToDisplay:=Left(strTMP, intCount - 1)
                           
                    Set rngToFind = .FindNext(rngToFind)
                   
                    If rngToFind Is Nothing Then Exit Do
                   
                Loop While rngToFind.Address <> strFirstAddr
            End If
       
         End With
    End Sub


    Regards, OssieMac

    Friday, July 25, 2014 7:01 AM
  • Hi,

    Based on your code, I find you are using a method "Application.Find". I cannot find such method in Excel Object Model. After testing, if you type in a string "abc" which don't contain "http://", it will throw out such error message "run-time error 13".

    I think OssieMac has provided a correct way for your reference. You should use the Range.Find Method instead of Application.Find method.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 25, 2014 8:36 AM
    Moderator