Macro doesn't run...

Hi everybody,

I have a small Makro, but it doesn't run... :-(

The Makro is supposed to look up the IP-Adress which it finds in the Logfile in a EXCEL-woksheet and take the corresponding CustomerID and write it into a NEW logfile.
NOW, somehow I get an Error like Runtime-Error 5: Invalid procedure or argument

I am not good with VBA, so I actually don't understand what I do wrong.... :-(

Sub Test() 
    Dim sText As String 
    Dim sPos As Long 
    Dim sKdNr As String 
    
    Open "C:\Temp\Test.txt" For Input As #1   'logfile
    Open "C:\Temp\Test1.txt" For Output As #2     ' new logfile with Customer IDs
    
    Do While Not EOF(1) 
        Line Input #1, sText 
        sPos = InStr(sText, " - - ") 
        sKdNr = KdNrSuchen(Mid(sText, 1, sPos - 1))    'and now I get the ERROR!!!!
        
        Print #2, sKdNr & " " & sText 
    Loop 
    
    Close 
End Sub 


Function KdNrSuchen(sIP As String) As String 
    Dim i As Long 
    
    For i = 1 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row 
        '  checking for the IP in column A
        If Trim(ActiveSheet.Cells(i, 1)) = Trim(sIP) Then 
'            if found then
            KdNrSuchen = Trim(ActiveSheet.Cells(i, 2)) 
            Exit For 
        End If 
    Next i 
End Function

Inside the code I marked where the error occurs....

THANKS!!

Hello JVLennox,

You don't mention what error you're getting, but I suspect that sPos is returning a zero at some point during the file read. If you have lines in the file that don't contain the search string " - - ", sPos will be zero. Place a breakpoint at this line in your code:

sKdNr = KdNrSuchen(Mid(sText, 1, sPos - 1))

Check the value sPos and then press F5 to step through your loop.

Sincerely,
Leith Ross

Wow, I guess I was blind... :-)
Well, thats what happens :-)

Thanks!