CODEWITHSUNDEEP
vbaexcelhyperlink
user3296894
user3296894

Reputation: 13

VBA Find matching cells and create hyperlinks to them

This is my first question on this site so I apologise if I make any glaring ommissions in my description of my problem.

I am working on a reference spreadsheet which can be used to configure a piece of software, i'm currently working on making it as user friendly as possible.

I have 2 Worksheets, the first one 'Main' is pulled from a database and represents a hierarchy of screens in the piece of software. (Effectively showing what you can access under each screen).

Some of these screens / objects tie into 'Popup' windows, these popup windows are also specified on the Main Sheet, Column "O".

My second sheet, 'Popup' contains further detail on each popup.

What I need is to run through Column "O" of Sheet "Main", and for every cell containing a value to search for that same value in Column "D" of Sheet "Popup", then create a hyperlink to the resulting row (any column, though I've been trying to link to "F").

    For i = 3 To LastRow
    If Cells(i, "O").Value <> "" Then
        k = Sheets("Main").Cells(i, "O").Value
            c = Application.Match(k, Sheets("Popup").Range("D:D"), 0)
            If IsError(c) Then
            Else
                Sheets("Main").Hyperlinks.Add Anchor:=Sheets("Main").Cells(i, "O"), SubAddress:="Popup!F" & c, TextToDisplay:=k
            End If
    End If
Next i

For the code above i'm currently getting the error 'Run-time error '450': Wrong number of arguments or invalid property assignment' On this line:

Sheets("Main").Hyperlinks.Add Anchor:=Sheets("Main").Cells(i, "O"), SubAddress:="Popup!F" & c, TextToDisplay:=k

Although this is the latest in a long list of various errors when trying to get this one row of code to work.

Any help would be greatly appreciate. David.

Upvotes: 1

Views: 2328

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Try this (Tried And Tested)

You were missing Address:=""

Sheets("Main").Hyperlinks.Add Anchor:=Sheets("Main").Cells(i, "O"), _
                              Address:="", _
                              SubAddress:="Popup!F" & c, _
                              TextToDisplay:=k

Upvotes: 1

Related Questions