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

 

[aa2000]

I mean a hyperlink that I have added by right clicking the cell and finding the directory etc. On this one the file was imported, then the error popped up

The other hyperlinks from which the file was not imported were written by a macro. This macro (written by someone else, not me) displays only the file name in the cell, not the file path like mine do

 

[Andrew Poulsom]

Using the TextToDisplay property shouldn't affect your code. Can you give an example of what FName contains when the macro fails? By the way why have you got 2 loops in your code?

 

[aa2000]

The loop for the integer i relates to the same integer i in the macro that is calling this one. Ie the cells(i, 1) is the same in both macros so that they both keep the same value. This may not eb the best way of doing it, but I can always optimize the code after I get it working I guess.

I cannot give the exact contents since my place of work forbids me from doing that (some strange confidentiality thing)
But its along the lines of
name = \\C:\Documents and Settings\Myname\Work\Excel\TextFiles\Text1.txt

Strangely this is exactly the same contents that is given when I use the hyperlink I added not the one the macro created. Interestingly the whole code and hyperlink process manages to import the file when it is copied into a new blank document, however it then gives the error.
On the original workbook that I want it to work with...No luck???

Thanks for sticking with this Andrew!

 

[Andrew Poulsom]

Just to be clear what's the error now? Can I see the ImportTextFile procedure please?

 

[aa2000]

The error is Runtime error 9 'Subscript out of range' and when I press debug it higlights the line starting FName = ...

Here is the ImportTextFile Procedure:

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
Dim StartFlag As Boolean

Application.ScreenUpdating = False
'On Error GoTo EndMacro:
SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row
StartFlag = False

Open FName For Input Access Read As #1
While Not EOF(1)
Line Input #1, WholeLine

    If WholeLine = "FUNKTIONEN" Then
    StartFlag = True
    End If
        If StartFlag = True Then
            If Right(WholeLine, 1) <> Sep Then
            WholeLine = WholeLine & Sep
            End If
        ColNdx = SaveColNdx
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
        Wend
        RowNdx = RowNdx + 1
        End If
        Wend
            Set Borderrange = Range("A3:AZ2000")
            For Each c In Borderrange.Cells
            If c.Value = "END" Then c.EntireRow.Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
            Next c
            
            Dim LastCol As Long
            LastCol = Cells.Find("*", Cells(Rows.Count, Columns.Count), _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            Columns(LastCol).Borders(xlEdgeRight).Weight = xlContinuous

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

The lines from Set border range to End macro create borders around the separate sets of data in the imported file.

 

[Andrew Poulsom]

The error is Runtime error 9 'Subscript out of range' and when I press debug it higlights the line starting FName = ...

I thought you said that FName contained a valid file name. So how come yo get an error on that line?

 

[aa2000]

The FName file name that is displayed is definitely valid, I have no idea why that line is highlighted if the file name is correct

 

[Andrew Poulsom]

Then I'm stumped. There is a hyperlink in that cell isn't there? What does this tell you if inserted before the offending line?

MsgBox Tabelle1.Cells(a, 1).Offset(0, 2).Hyperlinks.Item(1).Address

 

[aa2000]

Then I'm stumped

Join the club! The messagebox pops up with the name of the correct hyperlink so I'm completely perplexed as to whats happening.

Anyway Thanks for you help so far mate! I think at this point I'll speak to my colleagues about getting around this some way or nto having this particular function available

Cheers