CODEWITHSUNDEEP
vbaexcel
A Goh
A Goh

Reputation: 15

VBA run-time Error 9: subscript out of range

I'm running a vba to extract the url from a column of hyperlinked texts:

Sub RemoveHyperlinks()
Dim Cell As Range

Dim i As Long
Dim k As Long
k = 1
Set Cell = Workbooks("Company_List.xlms").Sheets("Sheet1").UsedRange

i = 0

Do Until i = Cell.Hyperlinks.Count
If Cell.Hyperlinks.Count > 0 Then
Workbooks("Company_List.xlms").Sheets("Sheet1").Cells(k, 1).Value = Cell.Hyperlinks.Item(1).Address
i = i + 1
k = k + 1
End If

Loop

End Sub

And the runtime error 9 occurs at the line:

Set Cell = Workbooks("Company_List.xlms").Sheets("Sheet1").UsedRange

Can anyone help with this? The Workbook name and Sheet name is correct, and my column data starts at A1 with heading title of column.

EDIT (2 Apr 2017): This piece of code has another error as it generated a list of url that appears to be the first one on the list. The entire list looks the same, but clicking through to each one would bring me to the correct hyperlink. I would like to have the list to have all the correct respective urls, how should I edit this code?

EDIT: I found the error, it should be Workbooks("Company_List.xlms").Sheets("Sheet1").Cells(k, 1).Value = Cell.Hyperlinks.Item(k).Address

Upvotes: 1

Views: 6216

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

If Workbooks("Company_List.xlms").Sheets("Sheet1").UsedRange returns subscript out of range then there is either not a workbook named "Company_List.xlms" in Workbooks or there is not a sheet named "Sheet1" in that workbook.

I suspect the first since the name is strange and should be "Company_List.xlsm" instead.

Upvotes: 3

Related Questions