coding error

[tom mcdonald]

I am importing a text file into excel and matching the acccount number
of column 1 with column 1 of the spreadsheet and inputting data into
column 4, and I keep getting the following error message.
"Account 10001 does not exist" even though it is the correct account
number :
here is the code , can anyone help me out on this.Thanks.



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 = Application.Match(sLine, 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))
End If
Loop
Close #iFno
End Sub

 

[anilsolipuram]

Backup your original workbook before try this macro.


Minor changes in the code

1) it should be WorksheetFunction.Match not activesheet.match
2)1st argument in Match should be sAccntNo instead of sline



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
MsgBox sLine
' account number is 1st 10 characters?
sAccntNo = Left$(sLine, 10)
' loop up the account number in the 1st column

vRow = WorksheetFunction.Match(CInt(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))
End If
Loop
Close #iFno
End Sub

 

[anilsolipuram]

Backup your original workbook before try this macro.


Minor changes in the code

1) it should be WorksheetFunction.Match not activesheet.match
2)1st argument in Match should be sAccntNo instead of sline



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(CInt(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))
End If
Loop
Close #iFno
End Sub