How to set a different link in each cell in a range?

Question

I'm programming a Macro in VB for Excel 2013 that search for coincidences in different worksheets, and add a link to the cells that match.

I'm havin torubles to insert the link in the cell, since the link must be different for a range of cells, I need help here.

Here is my code

Dim bufferDetails As String
Dim tmpCell As String
Dim spot As String
Dim cell As Variant
Dim cellSpots As Variant

For Each cell In Worksheets("MMS-Locations").Range("D2:D1833")
    If (cell.Value2 = "NULL") Then
        cell.Value2 = "NULL"
    Else
        tmpCell = cell.Text
        'A62
        If (Left(tmpCell, 3) = "A62") Then
            spot = spotName(tmpCell)
            For Each cellSpots In Worksheets("DetailedMap").Range("G60:CF123")
                If (cellSpots.Value2 = spot) Then
                    For Each linkToSpot In Worksheets("MMS-Locations").Range("H2:H1833")
                        Worksheets("MMS-Locations").Hyperlinks.Add _
                            Anchor:=Range(linkToSpot), _
                            Address:="http://example.microsoft.com", _
                            ScreenTip:="Microsoft Web Site", _
                            TextToDisplay:="Microsoft"

                    Next linkToSpot
                    Debug.Print ("Encontrado " + cellSpots)
                End If
            Next cellSpots
        End If 
    End If
Next cell

End Sub

Function spotName(fullName As String) As String

Dim realSpot As String
Dim lenght As Integer

lenght = Len(fullName) - 3
realSpot = Right(fullName, lenght)

spotName = realSpot

End Function

As I was thinking the linkToSpot variable contains the actual cell in the range, so I can move my selection of the sell, but my code fails in there with this error:

Error in the Range method of the '_Global' object,

Refer to: https://stackoverflow.com/questions/12174723/run-time-error-1004-method-range-of-object-global-failed — verses, Mar 07 '18 at 17:59
If you start off at the top with `Dim linkToSpot as Range`, then you should (I think) just be able to do `...Anchor:=linkToSpot, _ ...`. But you may even be able to do that now, without declaring `linkToSpot as Range`, but it's best practice to always declare your variables. OR, if you want (for whatever reason) to keep it with `Range()`, do this instead: `Anchor:=Range(linkToSpot.Address), _ ...`. — BruceWayne, Mar 07 '18 at 18:00
@BruceWayne thanks, this worked, but now this hole block `Worksheets("MMS-Locations").Hyperlinks.Add _ Anchor:=Range(linkToSpot.Adress), _ Address:="http://example.microsoft.com", _ ScreenTip:="Microsoft Web Site", _ TextToDisplay:="Microsoft"` Shows an error "Object Doesn't Support This Property or Method" — Mauricio Andrés, Mar 07 '18 at 18:47
And Even by adding the variable at the top, shows the same error as before — Mauricio Andrés, Mar 07 '18 at 18:50
With just `Anchor:=Range(linkToSpot)` make sure to add the worksheet `linkToSpot`'s range is coming from. I.e. `Anchor = Worksheets("MMS-Locations").Range(linkToSpot.Address)`. Also while you're troubleshooting, I'd put that whole thing on one line. Does this still throw an error? — BruceWayne, Mar 07 '18 at 18:56

score 0 · Answer 1 · answered Mar 07 '18 at 21:53

Just for reference, here is what I use to convert a phone number to an email for texting..setting it as a hyperlink in the current cell.

ActiveCell.Value = myNumbr 
Set myRange = ActiveCell 
ActiveSheet.Hyperlinks.Add anchor:=myRange, Address:="mailto:" & myRange.Value, TextToDisplay:=myRange.Value`

Keep your code simple to start with, until you find a working script, then add other items. Make good use of the F8 key to step through your code to find out exactly where an error occurs.

How to set a different link in each cell in a range?

1 Answers1