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.... :-(


Code:
--------------------
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!!


--
JVLennox
------------------------------------------------------------------------
JVLennox's Profile: http://www.excelforum.com/member.php...o&userid=32505
View this thread: http://www.excelforum.com/showthread...hreadid=563507

Macro doesn't run...
 

Hello JVLennox,

You don't mention what error you're getting, but I suspect that sPos i
returning a zero at some point during the file read. If you have line
in the file that don't contain the search string " - - ", sPos will b
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 Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=56350

Macro doesn't run...
 

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

Thanks

--
JVLenno
-----------------------------------------------------------------------
JVLennox's Profile: http://www.excelforum.com/member.php...fo&userid=3250
View this thread: http://www.excelforum.com/showthread.php?threadid=56350