CODEWITHSUNDEEP
vbaexcelhyperlinkrange
juiceb0xk
juiceb0xk

Reputation: 969

How can I apply my VBA code to a range of cells?

The purpose of my code acts upon the user input, which then automatically creates a hyperlink, based on what the user has input into the cell.

If the user types in hello into the cell, a hyperlink is created and links to www.google.com/hello.

Each cell will have a different input, so it is vital that my range extends from just hello, to other user inputs.

I have this working for one cell only, but when I try and span it out into multiple cells, I get a mis-match error, and I suspect that is due to me referencing cells wrong or something.

Here's my code:

Sub automaticHyperlink()

Dim link As String

link = "http://www.google.com/" & Range("F2")

Range("F2").Hyperlinks.Add Range("F2"), link

End Sub

When I change all range references from F2 to F2:F4, I then get an error.

My question is, how do I properly apply this code to a multiple range of cells without producing an error?

Any questions, then please let me know.

Thank you.

Upvotes: 0

Views: 167

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

First, I suggest putting what the text you want to display, as its own String. Then, simply loop through your range.

Sub automaticHyperlink()

Dim link As String, dispText As String
Dim cel As Range
Dim lastRow as Long

lastRow = Cells(rows.count,6).End(xlup).Row

For Each cel In Range("F2:F" & lastRow)
    dispText = cel.value
    link = "http://www.google.com/" & dispText
    cel.Hyperlinks.Add anchor:=cel, Address:=link, TextToDisplay:=dispText
Next cel

End Sub

Upvotes: 2

Related Questions