Printable View
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?
You can do it by using formula instead summing value in code:
Go into E1 cell (hit F2 on E1 cell), paste following formula :
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.Code:=SUM(1*OFFSET(E2,1,,COUNTA(A:A)-1))
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)
Code: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
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
yes, Suat, tks for your help.
You're welcome, sal21!