MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How could I solve the Cint( ) if the string is a 6-digit figure??


Posted by Andrew Cheung on June 15, 2001 7:15 AM

I have the following macro problem:
In the macro, I read the text file and and use the Cell function to perfome the "Match & Index" excel function, as the CInt () could only read date smaller than 6xxxxx (I have forgotten). (It is a primary key)
Could anyone tell me how to perform same Cint() function which can read 6-digit (Max 999999)??

Sub GL6113()

Dim InHandle As Integer
Dim ReadData As String
Dim Ccy As String
Dim GSLCode As String
Dim Amt As String
Dim Dirc As String
Dim iRow As Integer

InHandle = FreeFile()
iRow = 2

Sheets("working").Activate
Range("A2:D65535").ClearContents
Dirc = "C:\es\GL6113.txt" 'assume it is in default (working) folder

Open Dirc For Input As InHandle
Do While Not EOF(InHandle)
'On Error GoTo err_handler:
Line Input #InHandle, ReadData
If ReadData = "" Then
Exit Sub
End If

Ccy = Mid(ReadData, 1, 3)
GSLCode = Mid(ReadData, 4, 6)
Amt = Mid(ReadData, 11, 16)
If Ccy = "001" Or Ccy = "003" Then
Cells(iRow, 1) = Ccy
Cells(iRow, 2) = GSLCode
Cells(iRow, 3) = Amt
Cells(CInt(GSLCode), CInt(Ccy) + 9) = Amt
'iRow
iRow = iRow + 1
End If
Loop

Close InHandle
Exit Sub
'err_handler:
'End
End Sub


Posted by Ivan F Moala on June 15, 2001 8:14 AM

How about CLng( ) ??

InHandle = FreeFile() iRow = 2 Sheets("working").Activate Range("A2:D65535").ClearContents Dirc = "C:\es\GL6113.txt" 'assume it is in default (working) folder Open Dirc For Input As InHandle Do While Not EOF(InHandle) 'On Error GoTo err_handler: Line Input #InHandle, ReadData If ReadData = "" Then Exit Sub End If Ccy = Mid(ReadData, 1, 3) GSLCode = Mid(ReadData, 4, 6) Amt = Mid(ReadData, 11, 16) If Ccy = "001" Or Ccy = "003" Then Cells(iRow, 1) = Ccy Cells(iRow, 2) = GSLCode Cells(iRow, 3) = Amt Cells(CInt(GSLCode), CInt(Ccy) + 9) = Amt 'iRow iRow = iRow + 1 End If Loop