Excel-VBA: creating dynamic hyperlinks to google

Question

I have an excel sheet [Microsoft Office 2010] where the user can select a certain commodity & category, after which a list of suppliers linked to that commodity & category are printed on the sheet. I now want to print out hyperlinks next to this list of suppliers that perform a google search using the supplier as search term. This is the code I got right now: It checks whether the cells in column 6 are empty or not, if not that means a supplier name is printed in the cell. I then want a hyperlink to be printed in the column next to it that links to a google search using the suppliername as search term.

EDIT: code below works. Issue was in the if statement - isEmpty did not work for string value, but vbNullString fixed the issue.

Previous issueL The printed links lead to the general google home page, with no search terms. I believe the reason why the links are leading to general google pages is because the actual cell values (which are used as search term) are not read properly. The code line "If Not IsEmpty(cellSupplierListed) Then" always runs, even when the cells have no suppliername in there.. I'm not sure why. Also: Let's say there are 5 suppliers listed and the code reads over 300 rows (hard coded in code above), then still 300 links are printed out, while only 5 should have been printed out. (as only 5 of the 300 rows have values). Those 5 suppliers are printed out by previous code in the same sub and do indeed show up on the excel sheet. It just appears that the code below is not reading blank cells as being blank cells or non-blank cells as non-blank cells.

 Dim cellSupplierListed As String
 Dim csl As Integer
 Dim h As Integer


 h = 0

 For csl = 1 To 300  'needs to be updated if more than 300 suppliers are listed

 cellSupplierListed = Cells(9 + csl, 4).Value

If cellSupplierListed = vbNullString Then

    Exit For

Else

    h = h + 1
    Range("G" & (9 + h)).Hyperlinks.Add Range("G" & (9 + h)), "http://www.google.com/search?q=" & cellSupplierListed, , , "Link"

End If

Next csl

C.H. C.H. · Accepted Answer · 2016-08-16T18:38:14

From https://msdn.microsoft.com/en-us/library/office/ff822490.aspx

For Office 2013 and later, you can use the Hyperlinks.Add method

 .Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)

Example (from the above linked documentation):

With Worksheets(1) 
  .Hyperlinks.Add Anchor:=.Range("a5"), _ 
  Address:="http://example.microsoft.com", _ 
  ScreenTip:="Microsoft Web Site", _ 
  TextToDisplay:="Microsoft" 
End With

The following was tested in Office 2007:

Range("a5").Hyperlinks.Add Range("a5"), "http://www.google.com"

For the OP's actual question: Change the following line

Cells(7, 9 + h) = Hyperlink("http://www.google.com/search?q=" & cellSupplierListed, "Link")

to

Range("F"&(9+h)).Hyperlinks.Add Range("F"&(9+h)), "http://www.google.com/search?q=" & cellSupplierListed,,,"Link"

Excel-VBA: creating dynamic hyperlinks to google

2 Answers