I am importing data from a text file into spreadsheet and am trying to
match the account number incolumn 1 with column 1 of the spreadsheet ,
and if they match , then automatically input data in column 4. I am
using this code but get an error message saying account number "1000292
does not exist", but it does.
Can anyone point out where I am going wrong.
Thanks very much.

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


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

Tom,

you are matching the sLine not sAccntNr !
have some coffee <g

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


tom mcdonald wrote :


I am importing data from a text file into spreadsheet and am trying to
match the account number incolumn 1 with column 1 of the spreadsheet ,
and if they match , then automatically input data in column 4. I am
using this code but get an error message saying account number
"1000292 does not exist", but it does.
Can anyone point out where I am going wrong.
Thanks very much.

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

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380026