help again, last line required
 

In second last row of code i get error type mismatch, I'm nearly there
, but have been looking at this so long , I can't get it,
Can anyone please help me finish this off . Thanks again everyone for
all your help.



Sub Importdata()
Dim iFno As Integer
Dim sFName As Variant
Dim sLine As String, sAccntNo As String
Dim vRow As Variant

vFName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vFName = False Then Exit Sub

iFno = FreeFile()
Open vFName For Input As #iFno

Do While Not EOF(iFno)

Line Input #iFno, sLine

' account number is 1st 10 characters?
sAccntNo = Left$(sLine, 10)
' loop up the account number in the 1st column

vRow = WorksheetFunction.Match(CDbl(sAccntNo), ActiveSheet.Columns(1),
0)
If IsError(vRow) Then
MsgBox "Account " & sAccntNo & " does not exist", vbExclamation
Else
' week1 in column 4, week1's amount in input line at pos 22-26

ActiveSheet.Cells(vRow, 4) = CDbl(Mid$((sLine), 22, 5)) "error code 13
type mismatch"
End If
Loop
Close #iFno
End Sub


--
tom mcdonald
------------------------------------------------------------------------
tom mcdonald's Profile: http://www.excelforum.com/member.php...o&userid=24369
View this thread: http://www.excelforum.com/showthread...hreadid=380483

help again, last line required
 

Tom,
Check "sLine" as error suggests it is blank or non-numeric. Code
works OK for valid numeric data.

HTH

"tom mcdonald" wrote:


In second last row of code i get error type mismatch, I'm nearly there
, but have been looking at this so long , I can't get it,
Can anyone please help me finish this off . Thanks again everyone for
all your help.



Sub Importdata()
Dim iFno As Integer
Dim sFName As Variant
Dim sLine As String, sAccntNo As String
Dim vRow As Variant

vFName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vFName = False Then Exit Sub

iFno = FreeFile()
Open vFName For Input As #iFno

Do While Not EOF(iFno)

Line Input #iFno, sLine

' account number is 1st 10 characters?
sAccntNo = Left$(sLine, 10)
' loop up the account number in the 1st column

vRow = WorksheetFunction.Match(CDbl(sAccntNo), ActiveSheet.Columns(1),
0)
If IsError(vRow) Then
MsgBox "Account " & sAccntNo & " does not exist", vbExclamation
Else
' week1 in column 4, week1's amount in input line at pos 22-26

ActiveSheet.Cells(vRow, 4) = CDbl(Mid$((sLine), 22, 5)) "error code 13
type mismatch"
End If
Loop
Close #iFno
End Sub


--
tom mcdonald
------------------------------------------------------------------------
tom mcdonald's Profile: http://www.excelforum.com/member.php...o&userid=24369
View this thread: http://www.excelforum.com/showthread...hreadid=380483

help again, last line required
 

Hi Tom,

ActiveSheet.Cells(vRow, 4) = CDbl(Mid$((sLine), 22, 5))
"error code 13

Have you checked the length (i.e. =22) of the sLine variable?
Have you checked that sLine characters 22-26 are not alphas?

---
Regards,
Norman



"tom mcdonald"
wrote in message
news:tom.mcdonald.1qx6eb_1119261906.1316@excelforu m-nospam.com...

In second last row of code i get error type mismatch, I'm nearly there
, but have been looking at this so long , I can't get it,
Can anyone please help me finish this off . Thanks again everyone for
all your help.



Sub Importdata()
Dim iFno As Integer
Dim sFName As Variant
Dim sLine As String, sAccntNo As String
Dim vRow As Variant

vFName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vFName = False Then Exit Sub

iFno = FreeFile()
Open vFName For Input As #iFno

Do While Not EOF(iFno)

Line Input #iFno, sLine

' account number is 1st 10 characters?
sAccntNo = Left$(sLine, 10)
' loop up the account number in the 1st column

vRow = WorksheetFunction.Match(CDbl(sAccntNo), ActiveSheet.Columns(1),
0)
If IsError(vRow) Then
MsgBox "Account " & sAccntNo & " does not exist", vbExclamation
Else
' week1 in column 4, week1's amount in input line at pos 22-26

ActiveSheet.Cells(vRow, 4) = CDbl(Mid$((sLine), 22, 5)) "error code 13
type mismatch"
End If
Loop
Close #iFno
End Sub


--
tom mcdonald
------------------------------------------------------------------------
tom mcdonald's Profile:
http://www.excelforum.com/member.php...o&userid=24369
View this thread: http://www.excelforum.com/showthread...hreadid=380483

help again, last line required
 

Error may be that:
1) text file from which you retrieving sline has less than 2
charecters in line
2)sline has text datatype instead of double datatype in sline line fro
charecters 22 to 27

I added debug which would alert with message of sline text and lengt
of line, and then charecter between 22 to 27

Let me know what alert message you get, and where is the error message

Sub Importdata()
Dim iFno As Integer
Dim sFName As Variant
Dim sLine As String, sAccntNo As String
Dim vRow As Variant

vFName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If vFName = False Then Exit Sub

iFno = FreeFile()
Open vFName For Input As #iFno

Do While Not EOF(iFno)

Line Input #iFno, sLine

' account number is 1st 10 characters?
sAccntNo = Left$(sLine, 10)
' loop up the account number in the 1st column

vRow = WorksheetFunction.Match(CDbl(sAccntNo), ActiveSheet.Columns(1)
0)
If IsError(vRow) Then
MsgBox "Account " & sAccntNo & " does not exist", vbExclamation
Else
' week1 in column 4, week1's amount in input line at pos 22-26
msgbox sline & " length:" & len(sline)
msgbox Mid$((sLine), 22, 5)

ActiveSheet.Cells(vRow, 4) = CDbl(Mid$((sLine), 22, 5)) "error code 1
type mismatch"
End If
Loop
Close #iFno
End Su

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=38048