Consulting

Results 1 to 5 of 5

Thread: summ durind the importing...

  1. #1

    summ during the importing...

    The zip file contain a wbook and a txt file.
    for your experiment put the txt file (REGIONE.02 in a floppy disk)

    My problem:

    Is possible during the import of txt file, insert the total of the variable imported(in module2) VAR_IMPORTO1 in the cell E1 of the sheet REGIONE_DISK?

  2. #2
    BoardCoder VBAX Regular
    Joined
    May 2004
    Location
    Istanbul, Turkiye
    Posts
    73
    You can do it by using formula instead summing value in code:

    Go into E1 cell (hit F2 on E1 cell), paste following formula :

    =SUM(1*OFFSET(E2,1,,COUNTA(A:A)-1))

    then hit Ctrl+Shift+Enter. It is an array formula so you must enter it by using these 3 keys. You will see that it will return correct total and it will get the necessary range of rows.

    Or you can still accomplish this by changing your REGION_DISK sub routine with the following one (this is what you exactly asked for but that I suggested formula instead)

    [VBA]Sub REGIONE_DISK()
    Dim totVAR_IMPORTO1
    If CheckCondition() = False Then
    'Conditions are false => stop the macro
    End
    End If
    Set Elenco = Worksheets("REGIONE_DISK")
    n = FirstFree("REGIONE_DISK", "A", 2) 'Record iniziale per il riempimento del tabulato
    iFile = FreeFile()
    NomeFile = "a:\REGIONE.02"
    'Ciclo apertura file di testo
    Open NomeFile For Input As #iFile
    Do Until EOF(iFile)
    Line Input #iFile, Record_Corrente$

    If Mid(Record_Corrente, 30, 1) = "." Then
    VAR_MANDATO = Mid(Record_Corrente, 12, 8)
    VAR_ID = Mid(Record_Corrente, 2, 10)
    VAR_NOMINATIVO = Trim(Mid(Record_Corrente, 40, 40))
    VAR_IMPORTO1 = Format(nstr(Trim(Mid(Record_Corrente, 80, 13))) / 100, "#,##0.00")
    totVAR_IMPORTO1 = totVAR_IMPORTO1 + CCur(VAR_IMPORTO1)
    VAR_DATA = DateValue(Mid(Record_Corrente, 33, 2) + "/" + Mid(Record_Corrente, 35, 2) + "/20" + Mid(Record_Corrente, 37, 2))
    VAR_DATA1 = DateValue(Mid(Record_Corrente, 93, 2) + "/" + Mid(Record_Corrente, 95, 2) + "/20" + Mid(Record_Corrente, 97, 2))
    VAR_DATA2 = DateValue(Mid(Record_Corrente, 99, 2) + "/" + Mid(Record_Corrente, 101, 2) + "/20" + Mid(Record_Corrente, 103, 2))

    'Istruzione per la localizzazione di doppioni nell'elenco

    Set Found_MANDATO = Elenco.Columns("A:A").Find(Val(VAR_MANDATO), lookat:=xlWhole)
    If Found_MANDATO Is Nothing Then

    Elenco.Range("A" + CStr(n)).Value = Val(VAR_MANDATO)
    Elenco.Range("B" + CStr(n)).Value = VAR_ID
    Elenco.Range("D" + CStr(n)).Value = VAR_NOMINATIVO
    Elenco.Range("E" + CStr(n)).Value = VAR_IMPORTO1
    Elenco.Range("F" + CStr(n)).Value = VAR_DATA
    Elenco.Range("G" + CStr(n)).Value = VAR_DATA1
    'Avanzamento contatore nel Display
    Elenco.Range("D1").Value = n - 2
    'Verifica presenza VAR_MANDATO nel foglio REGIONE E CALCOLO COMMISSIONI
    Set Found_GOT = Worksheets("REGIONE_GOT").Columns("H:H").Find(Val(VAR_MANDATO), lookat:=xlWhole)
    If Not Found_GOT Is Nothing Then
    IMPORTO_GOT = Format(nstr(Worksheets("REGIONE_GOT").Range("G" + CStr(Found_GOT.Row)).Value) / 100, "#,##0.00")
    If IMPORTO_GOT <> VAR_IMPORTO1 Then
    VAR_COMM = Format(IMPORTO_GOT - VAR_IMPORTO1, "#,##0.00")
    Elenco.Range("C" + CStr(n)).Value = VAR_COMM
    Worksheets("REGIONE_GOT").Range("J" + CStr(Found_GOT.Row)).Value = VAR_COMM
    End If
    End If
    'Avanzamento record nell'elenco
    n = n + 1
    End If
    'End If
    'End If
    End If

    Loop

    Close #iFile ' Chiusura Handle file di testo

    Elenco.Range("E1").Value = totVAR_IMPORTO1
    End Sub[/VBA]


    I hope it helps.
    Suat

  3. #3
    BoardCoder VBAX Regular
    Joined
    May 2004
    Location
    Istanbul, Turkiye
    Posts
    73

    Formula solution attachment

    I just noticed that your E1 cell is set to Text, so you should first set it as General (or number) then enter the formula I suggested above.

    It will show #REF when there is no data but it will calculate the result once you execute your macro.

    Attached is the formula solution. Click your original button and see how it is working. No change is necessary in VBA code in this solution.

    Suat

  4. #4
    yes, Suat, tks for your help.

  5. #5
    BoardCoder VBAX Regular
    Joined
    May 2004
    Location
    Istanbul, Turkiye
    Posts
    73
    You're welcome, sal21!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •