0

I'm new to this site, but I have already found some nice advice on how to solve problems in VBA. Now I'm here to ask help on a sub that gives me problems with Hyperlinks. In particular, my problem is similar to the one described in this topic:

Excel VBA Get hyperlink address of specific cell

I have a worksheet full of hyperlink, but I need to extract only the addresses present in the "H" column, starting from "H6" and writing them into the "N" column, starting from "N6".

I put down this code:

Sub EstraiIndirizzoPut()

Dim IndirizzoInternet As Hyperlink
Dim ISINs As String

i = 6

For Each IndirizzoInternet In Sheets("XXX").Range("H" & i).Hyperlinks
IndirizzoInternet.Range.Offset(0, 6).Value = IndirizzoInternet.Address
ISINs = Mid(IndirizzoInternet.Address, 78, 12)
Range("N" & i).Value = ISINs
i = i + 1

Next

End Sub

It works fine only for the first "H6" cell, but at the "Next" point, when it should read the "H7" cell, it goes instead to "End Sub", terminating the routine, altough the "H7" cell, as well many others down the column, are filled with hyperlinks (it gives me "Nothing" value). Could you please suggest me where I get this wrong? Many thanks.

braX
  • 11,506
  • 5
  • 20
  • 33
GPace
  • 3
  • 4

1 Answers1

0

Your loop isnt set up correctly. Try it like this instead:

For i = 6 to 100
  Set IndirizzoInternet = Sheets("XXX").Range("H" & i).Hyperlinks
  IndirizzoInternet.Range.Offset(0, 6).Value = IndirizzoInternet.Address
  ISINs = Mid(IndirizzoInternet.Address, 78, 12)
  Range("N" & i).Value = ISINs
Next

How do you know when to stop the loop? Is it a preset number of rows? If it not, you will want to have something determine the last row to process and replace the 100 with that variable.

braX
  • 11,506
  • 5
  • 20
  • 33
  • Hello braX, thank you for the answer. I tried your solution, but debugging at the line Set IndirizzoInternet = Sheets("XXX").Range("H" & i).Hyperlinks it gives me the run-time error '13': "Type mismatch". Maybe I should put a do while loop? Thanks again. – GPace Jan 18 '18 at 16:55
  • Try changing this: `Dim IndirizzoInternet As Hyperlink` to this `Dim IndirizzoInternet As Variant` – braX Jan 18 '18 at 17:37
  • HI braX, thanks again. This problem is solved with the "Variant " type, but now it gives me another error on the subsequent line IndirizzoInternet.Range.Offset(0, 6).Value = IndirizzoInternet.Address with the run-time error '438': Object doesn't support this property or method. I think I should review entirely the code. – GPace Jan 19 '18 at 10:36