Search code examples
excelvba

Excel VBA Macro Returning "Subscript Out of Range" for Incremental Function Converting Hyperlinks to Raw URLs

I am writing an Excel VBA macro that is working fine except for one specific function, and I cannot figure out why it is failing; I'm not a programmer, although I do have some understanding of the basic logic, just little/no experience at writing it, so apologies in advance if there is any confusion imparted by my attempted explanations. The error returned when attempting to execute the code in question is "Run-time error code '9': subscript out of range", and I've copied the relevant code snippets below:

' Define variable for worksheet in question
Dim wsSales As Worksheet
Set wsSales = ThisWorkbook.Sheets("Sales")

' Find last row with data in it
Dim lastRowSales As Long
lastRowSales = wsSales.Cells(Rows.Count, "J").End(xlUp).Row

' Loop through column J and convert hyperlinks to raw URLs
For i = 2 To lastRowSales
    If wsSales.Cells(i, "J").Hyperlinks.Count > 0 Then
        wsSales.Cells(i, "J").Value = wsSales.Hyperlinks(i).Address
    End If
Next i`
  • For extra info/context, column J of the Sales sheet referenced contains hyperlinked text (e.g., "Object Name" that points to a URL in a sales-related webpage), and I'm trying to get the actual URL for each row in the range so I can output it elsewhere. Row 1 is a header row, so I'm starting with 'i = 2' to ignore it accordingly.
  • What the above code ends up doing is partially successful, but specifically fails on the last row for some reason. So if I have, for example, 100 rows in column J of the Sales sheet (99 rows with data and 1 header row), it will successfully convert any hyperlinked values to a URL for the first 99 rows, but row 100 does not convert and Excel spits out the 'subscript out of range' error. When looking at the highlighted code that failed after clicking 'Debug' on the error pop-up in the VBA Editor, it is specifically the 'wsSales.Hyperlinks(i).Address' part that returns a value of ''.
  • Additionally, it does not actually convert things quite properly; for example, say that row 50 has a hyperlinked text string in it. Rather than converting cell J50 to show the URL that was in J50, it actually shows the URL for J51, and it does this for the entire range (where it's showing the URL of the cell below it, not the cell itself).
  • If I start with 'i = 1' instead to include checking the header row (which will never have a hyperlink, but I figured was worth testing), the function works identically - same behavior, same error, no difference at all relative to starting with 'i = 2'. That seems to imply to me the error is somewhere either in the logic before the function actually executes or my references in the function itself.
  • I have also tested the above code with "wsSales.Hyperlinks(1).address" (1 instead of i) and it ends up completing successfully but using the same URL for the entire column J, so there seems to be a flaw with that logic as an alternative (presumably the static reference for the Hyperlinks object). The same is true if I use '2' instead of '1', so I suspect that using any digit will give me the same core problem.
  • I feel like there must be something wrong with either my function or some variable I've defined that is causing this, but after looking extensively through my code and attempting to 'rubber ducky' troubleshoot it, I'm still coming up blank.

I've used essentially the exact same logic for multiple other formulas that compose the rest of the larger macro and they all work properly, but this function specifically fails to work as expected; every other 'for i = # To [value]' iterates successfully and commenting out the above code snippet from the larger macro enables the full macro to work exactly as expected, just not this function. Does anyone have any thoughts or suggestions for why this may be failing to function as expected? Any ideas for what logic I should check, what may be failing, or a better way to do this? Any advice would be greatly appreciated, thanks!

Solution

  • Refer to the hyperlink in each specific cell, not the Worksheet.Hyperlinks collection:

    For i = 2 To lastRowSales
    If wsSales.Cells(i, "J").Hyperlinks.Count > 0 Then
        wsSales.Cells(i, "J").Value = wsSales.Cells(i, "J").Hyperlinks(1).Address
    End If
Next i`

    In other words, you want to use the Range.Hyperlinks property.

    If you did want to use the Worksheet.Hyperlinks approach:

    Dim h As Hyperlink
For Each h In wsSales.Hyperlinks
    h.Range.Value = h.Address
Next