locked
EXCEL VBA - version 2003 - analysis big numbers RRS feed

  • Question

  • Hello,

    How I can check the numbers? The big number?

    I have a text file.

    11111111111111111000001

    11111111111111111000002

    ...

    1111111111111111100XXXX

    Problem: int or long not possible.

      Only check a substring?

    After that write a new textfile.

    11111111111111111000001; IO

    11111111111111111000002; IO

    11111111111111111000005; NIO

    Conditions are:

       IO, if the step to the preview number 1 or equal

       NIO, if the step to the preview number more than 1.

     Greeting Andreas


    Wednesday, August 1, 2012 6:52 PM

Answers

  • Andreas,

    Try a macro like this, that opens the file as text and processes the values.  I have assumed that only the last 6 digits of the string matter. The file "Output.Txt" will be written into the folder where the workbook with the macro is stored.

    Bernie

    Sub ReadAndWrite()
        Dim FileNum As Integer
        Dim Last_Row As Long
        Dim MYFILE As String
        Dim i As Long

        MYFILE = ThisWorkbook.Path & "\"

        FileNum = FreeFile

        Open MYFILE & "Output.Txt" For Output As #FileNum

        Workbooks.OpenText Filename:=Application.GetOpenFilename( _
            "Text Files (*.txt),*.txt", , "Pick the text file"), Origin:=437, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=False, FieldInfo:=Array(1, 2)
       
        Last_Row = Cells(Rows.Count, 1).End(xlUp).Row

        Print #FileNum, Cells(1, 1).Value & "; IO"

        For i = 1 To Last_Row - 1
            If CLng(Right(Cells(i + 1, 1).Value, 6)) - CLng(Right(Cells(i, 1).Value, 6)) > 1 Then

                Print #FileNum, Cells(i + 1, 1).Value & "; NIO"
            Else
                Print #FileNum, Cells(i + 1, 1).Value & "; IO"
            End If
        Next i
        ActiveWorkbook.Close False

        Close #FileNum
    End Sub


    HTH, Bernie

    Wednesday, August 1, 2012 8:28 PM