CODEWITHSUNDEEP
vbaexcel
miljon
miljon

Reputation: 2771

Why my vba script changes all hyperlinks together instead individually

first of all I'm a not good at vba, I used many tuts, but It's not what I want ;)

What I'm trying to accomplish: Select range of hyperlinks in spreadsheet and set hyperlinks to call another spreadsheet cells (always) from A2 to AX (depends on how many rows I selected). (Sorry for not proper naming, last time I used vba was about 10y ago)

Before run a script: all hyperlinks are set to different spreadsheet to call cell A2, like this: CommLinkItem_57!A2

Important: it can't be used =HYPERLINK(cell;name) function, couse another script is using this spreadsheet and It not work with this function

After run a script: hyperlinks are not incremented from A2 to AX, instead all hyperlinks (event those that I not selected) are calling last iterated element witch is AX

Sub LoopSelection()

    Dim cel As Range
    Dim selectedRange As Range
    Dim aa As String
    Dim counter As Integer
    counter = 2

    Set selectedRange = Application.Selection

    For Each cel In selectedRange.Cells
        Debug.Print cel.Address & " " & cel.Hyperlinks.Count
        If cel.Hyperlinks.Count > 0 Then
            aa = cel.Hyperlinks.Item(1).SubAddress
            If cel.Hyperlinks.Item(1).SubAddress Like "*!*" Then
                cel.Hyperlinks.Item(1).SubAddress = Trim(Split(aa, "!")(0)) & "!A" & counter
            End If
            counter = counter + 1
            Debug.Print cel.Hyperlinks.Item(1).SubAddress
        End If
    Next cel

End Sub

For example i select 10 cells form I10 to I20 and then I run a script.. My output in console is like this:

$I$10 1
CommLinkItem_57!A2
$I$11 1
CommLinkItem_57!A3
$I$12 1
CommLinkItem_57!A4
$I$13 1
CommLinkItem_57!A5
$I$14 1
CommLinkItem_57!A6
$I$15 1
CommLinkItem_57!A7
$I$16 1
CommLinkItem_57!A8
$I$17 1
CommLinkItem_57!A9
$I$18 1
CommLinkItem_57!A10
$I$19 1
CommLinkItem_57!A11
$I$20 1
CommLinkItem_57!A12

(works fine, finds proper cells (I10:I20), finds one hyperlink, finds spreadsheet named CommLinkItem_57 and set (in console output) proper incremented cell value from A2 to A12

So in excel cell I10 and I20 are calling CommLinkItem_57!A12. And that's a problem.. Can you point where I made mistake, and how to fix that problem

Upvotes: 1

Views: 73

Answers (3)

miljon
miljon

Reputation: 2771

Like @Excelosaurus said, all hyperlinks were reference like, and when I changed one, all were changed too. So I make workaround and create hyperlinks from basics:

  1. I'm counting from A2 to AX so counter is set to 2
  2. Name of table where nested cells are always is in the same column in index 2, so table name sets row 2, and column of a selected range and takes value of cell i.e. tableName
  3. Hyperlinks are created only in active sheet, line: With Worksheets(Application.ActiveSheet.Index)
  4. If we don't want address to url or file, make Address property, i.e. empty quote ""

I think rest is self-explanatory in code:

Sub LoopSelection()

    Dim selectedRange As Range
    Dim counter As Integer
    Dim tableName As String

    counter = 2
    Set selectedRange = Application.Selection
    tableName = Cells(2, selectedRange.Column).Value

    For Each cel In selectedRange.Cells

        With Worksheets(Application.ActiveSheet.Index)
        .Hyperlinks.Add Anchor:=.Range(cel.Address), _
        Address:="", _
        SubAddress:=tableName & "!A" & counter, _
        TextToDisplay:=tableName
        End With

        counter = counter + 1
    Next cel

End Sub

Upvotes: 0

Excelosaurus
Excelosaurus

Reputation: 2849

Your code is OK. The problem is that worksheets maintain a HyperLinks collection of distinct URLs. I suspect your initial URLs are all the same, hence you're always updating the same HyperLink and end up with the one with the highest counter value. If possible, make your initial URLs distinct.

Upvotes: 1

Vityata
Vityata

Reputation: 43575

From what I see the counter should be out of the condition. Like this:

For Each cel In selectedRange.Cells
    counter = counter + 1

    Debug.Print cel.Address & " " & cel.Hyperlinks.Count
    If cel.Hyperlinks.Count > 0 Then
        aa = cel.Hyperlinks.Item(1).SubAddress
        If cel.Hyperlinks.Item(1).SubAddress Like "*!*" Then
            cel.Hyperlinks.Item(1).SubAddress = Trim(Split(aa, "!")(0)) & "!A" & counter
        End If
        Debug.Print cel.Hyperlinks.Item(1).SubAddress
    End If
    'or put the counter here, it depends on your code...
Next cel

Upvotes: 0

Related Questions