Trying to get Macro to apply to a set Range

[Groovicles]

hi everyone,

I have the following code I found online which seems to do what essentially need it to do with a couple of small adjustments:

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

What I'm trying to do is have this code automatically applied to cell ranges K1:K3000 and M1:M3000. I've tried a dozen variations and it keep getting an error code 13 each time on the intCount=Application line. Any ideas?

Thanks very much for your help.

[Norie]

If you want to find out where a substring is in a string then use InStr.

intCount = InStr(strTMP, "http://")

[Groovicles]

Hi Norie,

Thanks. The code appears to work fine when I do not try and set a specific range. Do you have any ideas how I could apply the two ranges i listed above to the code?

Thanks!

[Norie]

I don't see why it wouldn't work for specific range(s) provided you have selected them before running the code.

You might try using Selection.Cells just to make sure the loop is going through cells.

[Groovicles]

Hi Norie,

What I'm trying to do is have the range of cells pre-selected in the code itself rather than selecting the range manually and then running the code.

[Norie]

If you wanted the code to run on a specific range change Selection to refer to that specific range.

 For Each rngRange In Range("K1:K3000, M1:M3000")

[Groovicles]

Hi Norie,

I tried that method, and I applied it again at your suggestion. I got error code 5: Invalid procedure call or argument and the following line highlighted:

rngRange.Hyperlinks.Add Anchor:=Cells(rngRange.Row, rngRange.Column), Address:=Mid(strTMP, intCount), ScreenTip:=Mid(strTMP, intCount), TextToDisplay:=Left(strTMP, intCount - 1)

Arg!!!!

[Norie]

Did you add a check for intCount not being 0?

It will be 0 if "http://" is not found in the cell.

[Groovicles]

Hi Norie,

My VBA skills are quite basic. How would I add a check for intCount not being 0?

[Norie]

Perhaps.

Sub Apply_Hyperlinks()
Dim intCount As Integer
Dim rngRange As Range
Dim strTMP As String

    For Each rngRange In Range("K1:K3000, M1:M3000")

        If Trim(rngRange.Value) <> "" Then
            strTMP = rngRange.Text
            intCount = InStr(strTMP, "http://")
            If intCount<> 0 Then
                rngRange.Hyperlinks.Add Anchor:=Cells(rngRange.Row, rngRange.Column), Address:=Mid(strTMP, intCount), ScreenTip:=Mid(strTMP, intCount), TextToDisplay:=Left(strTMP, intCount - 1)
            End If
        End If

    Next rngRange

End Sub

[Groovicles]

Hi Norie,

Thanks for the suggestions. I tried it but got an error code 13 with the

If Trim(rngRange.Value) <> "" Then

line highlighted.

Any thoughts?

[Norie]

That's a problem with the data not the code.

[Groovicles]

Hi Norie,

Well the contents of the cells in those ranges are all text strings or blank. There are no formula in any of the cells. In your opinion, what in particular might cause such an error if its the data and and not the code?

[Groovicles]

Figured it out. thanks for your input, Norie.