Help solving Run Time error 9

[aa2000]

Hi folks

I have a macro, that when called will look through column 1 in sheet 2 and compare the text to column 1 sheet 1. If they match it will select the cell 2 to the left in sheet 1 and try and follow the filepath of the hyperlink in that cell.

My code is:

Sub Importselect()
Dim a As Integer

For i = 3 To 15000
For a = 3 To 15000
name = Tabelle1.Cells(a, 1).Offset(0, 2).Hyperlinks.Item(1).Address
If Tabelle2.Cells(i, 1) = Tabelle1.Cells(a, 1) Then ImportTextFile FName:="name", Sep:="   "
Next a
Next i

End Sub

However when I run this macro I receive runtime error 9: Subscript out of range, and pressing debug highlights the following line:

If Tabelle2.Cells(i, 1) = Tabelle1.Cells(a, 1) Then ImportTextFile FName:="name", Sep:="   "

I believe the problem lies in the fact that it is not actually picking up the filepath, but I'm not sure and it could be elsewhere.

Any ideas on how to solve this? I'd really appreciate any sort of help!

Thanks

 

[Andrew Poulsom]

Why is name surrounded by double quotes?

 

[aa2000]

I thought that since when I manually add a filepath, it is in quotes, that name would also need to be.

Without putting it in quotes I get the Compile Error 'ByRef Argument type mismatch'
Not sure what that means?

 

[Andrew Poulsom]

What is ImportTextFile?

 

[aa2000]

ImportTextFile is another public sub that is called by this one. That part of the code works correctly in other situations and has been adapted from code I found on this site:
http://www.cpearson.com/excel/ImpText.aspx

Sorry for the poor info Andrew!

 

[Andrew Poulsom]

Try this:

Sub Importselect()
    Dim i As Integer, a As Integer, FName As String
    For i = 3 To 15000
        For a = 3 To 15000
            FName = Tabelle1.Cells(a, 1).Offset(0, 2).Hyperlinks.Item(1).Address
                If Tabelle2.Cells(i, 1) = Tabelle1.Cells(a, 1) Then ImportTextFile FName:=FName, Sep:="   "
        Next a
    Next i
End Sub

I changed the name of your variable because it's unwise to use an existing VBA property.

 

[aa2000]

Unfortunately that too returns the same Runt time error no. 9. I am beginning to think that the problem maybe due to the hyperlink itself rather than with the code. What do you think?

I will try it on another hyperlink that I manually added and see fi the problem remains and fet back to you.

 

[Andrew Poulsom]

What is FName when the macro fails (hover your mouse over it)?

 

[aa2000]

Fname is the correct file path that I am trying to import, so that at least works correctly. Thank you for that.
When I tried it with a manually inserted hyperlink that dispalyed the full file path in the cell rather than just the file name it imported it fine then displayed the same runtime error. So now I have no idea what that runtime error is connected to.

 

[Andrew Poulsom]

What do you mean by "manually inserted hyperlink"? What sort of hyperlinks do you have?