help again, last line required

  • Thread starter Thread starter tom mcdonald
  • Start date Start date
T

tom mcdonald

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,
Check "sLine" as error suggests it is blank or non-numeric. Code
works OK for valid numeric data.

HTH
 
Hi Tom,
ActiveSheet.Cells(vRow, 4) = CDbl(Mid$((sLine), 22, 5))
"error code 13
Click to expand...

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" <[email protected]>
wrote in message
 
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
 
Back
Top