coding help again

[tom mcdonald]

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

[keepITcool]

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

[anilsolipuram]

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