Creating a hyperlink for a cell found by FIND function

Question

I wrote VBA code to search for a word in "Sheet1". The list of words are in "Sheet2". The results are published in "Sheet3" along with the word and all the cell addresses of the word found in "Sheet1".

The cell addresses are published as "$B$26" for example. I want this to be a hyperlink to cell B26 of Sheet1.

I used the below code.

Worksheets("Sheet3").Activate

'Record the address of the data, in the current workbook.

     With ThisWorkbook.ActiveSheet.Range("D2")
        .Value = "Address of variable:"
        .Offset(0, -1).Value = "Variable Name"
        .Offset(0, -2).Value = "No of usages"
        .Offset(i, 0).Value = GCell.Address
        .Offset(i, -1).Value = Txt
        .Columns.AutoFit
        .Offset(i, 1).Columns.AutoFit

If GCell Is Nothing Then Exit Sub

    Sheets("Sheet3").Hyperlinks.Add Anchor:=Sheets("Sheet3").Cells(i,0), _
         Address:="", _
         SubAddress:="'" & Sheets("Sheet1").Name & "'!" & GCell.Address, TextToDisplay:="Click"

I get

Run time error '1004': Application-defined or object-defined error

on the above line. GCell is the range where the word is found.

*"But I get a error on the above line"* Without telling **which** error this information is pretty useless. Please also include the relevant code part (one line is not enough). Did you check `GCell` for `Nothing` after using `Find`? — Pᴇʜ, Feb 14 '19 at 09:19
Sorry about that. Edited now. Yes I check GCell for Nothing after using FIND. — Karthi, Feb 14 '19 at 09:30
Yes it did work! Thanks a lot.. was checking every other thing!! Thank you PEH.. — Karthi, Feb 14 '19 at 11:02

Pᴇʜ · Accepted Answer · 2019-02-14T10:20:48.243

2

The issue here is .Cells(i,0).

Row/Column numbering starts with 1 not 0 so the column 0 does not exist, therefore you get an error. Also ensure that i is >0.

And I highly recommend to avoid using .Activate and ActiveSheet instead reference your worksheets by their name. You might benefit from reading How to avoid using Select in Excel VBA.

This …

Worksheets("Sheet3").Activate
With ThisWorkbook.ActiveSheet.Range("D2")

can be written as …

With ThisWorkbook.Worksheets("Sheet3").Range("D2")

edited Feb 14 '19 at 10:20

answered Feb 14 '19 at 09:43

Pᴇʜ

56,719
10
49
73

1

Yes it did work! Thanks a lot.. was checking every other thing!! Thank you PEH! And yes I eliminated the use of '.Activate' as well. Thanks!!! :) – Karthi Feb 14 '19 at 11:03

Creating a hyperlink for a cell found by FIND function

1 Answers1