Sum during the importing...

[sal21]

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?

[smozgur]

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)

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

I hope it helps.
Suat

[smozgur]

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

[sal21]

yes, Suat, tks for your help.

[smozgur]

You're welcome, sal21!