You are here:Home-Domande su Excel VBA e MACRO-somma da più fogli
somma da più fogli2021-08-13T10:53:57+02:00

Home Forum Domande su Excel VBA e MACRO somma da più fogli

Taggato: 

Visualizzazione 21 filoni di risposte
  • Autore
    Post
    • luigi.castoluigi.casto
      Partecipante
        Post totali: 99

        http://www.filedropper.com/mio

         

        Buongiorno a tutti ,

        avrei bisogno di calcolare la somma relativa al nominativo in colonna A prendendo i dati dai soli fogli denominati con Data nei range a dx dello stesso

        i fogli aumentano giornalmente e il numero di righe è dinamico in base ad inserimenti o cancellazioni dei soggetti presenti

        Vi ringrazio dell’aiuto

        buona giornata

      • AvatarKris_9951
        Partecipante
          Post totali: 226

          Ciao Luigi.Casto

          Prova questa soluzione e fai sapere (se vuoi) :bye: :bye:
          https://www.dropbox.com/s/7myaqnmxbxlizak/SommaOreLavoro.xlsm?dl=0

          Sub TotaleOre()
              Dim wsTotali As Worksheet
              Dim matrNomi() As Variant
              Dim rngNomi As Range
              
              Set wsTotali = Sheets("Totali")
              Set rngNomi = wsTotali.Range("A1").CurrentRegion.Columns(1).Offset(1, 0)
              Set rngNomi = rngNomi.Resize(rngNomi.Rows.Count - 1, 1)
              
              If wsTotali.Cells(2, 1).Value <> vbNullString Then
                  If rngNomi.Cells.Count > 1 Then
                      matrNomi = rngNomi.Value
                      ReDim Preserve matrNomi(1 To rngNomi.Cells.Count, 1 To 4)
                  Else
                      ReDim matrNomi(1 To 1, 1 To 4)
                      matrNomi(1, 1) = rngNomi.Value
                  End If
                  
                  Call Mod_TotOre.creaMatrice(matrNomi)
              
                  With wsTotali
                      .Range("A1").CurrentRegion.Offset(1, 0).ClearContents
                      .Range("A2").Resize(UBound(matrNomi), 4).Value = matrNomi
                  End With
                  MsgBox "Aggiornamento effettuato", vbInformation, "Operazione eseguita"
              Else
                  MsgBox "Impossibile procedere!" & Chr(13) & "Il campo nomi non sembra essere popolato correttamente!", vbCritical, "Errore!"
              End If
              
              Set wsTotali = Nothing
              Set rngNomi = Nothing
              Erase matrNomi
          End Sub
          
          Sub creaMatrice(matrice() As Variant)
              Dim ws As Worksheet
              Dim x As Long, j As Long, k As Long
              
              For Each ws In ThisWorkbook.Worksheets
                  If IsDate(ws.Name) Then
                      x = 2
                      Do While ws.Cells(x, 1).Value <> vbNullString
                          For j = LBound(matrice) To UBound(matrice)
                              If matrice(j, 1) = ws.Cells(x, 1).Value Then
                                  For k = 2 To 4
                                      matrice(j, k) = matrice(j, k) + (ws.Cells(x, k).Value * 24)
                                  Next k
                              End If
                          Next j
                          x = x + 1
                      Loop
                  End If
              Next ws
          End Sub
          
        • luigi.castoluigi.casto
          Partecipante
            Post totali: 99

            Ciao Kris ,

            grazie del tuo intervento ,

            solo una cosa , il file allegato da me è simile ma non identico all’originale per via dei dati sensibili e cercando di adattare il tuo codice non riesco proprio a farlo funzionare .

            Sicuramente il codice va bene per quello che devo ottenere ma il mio file ha un numero maggiore di righe e colonne e non sono statiche , potrebbero aumentare come diminuire.

            Magari potresti solo inserire dei commenti alle righe di codice in modo da capirne il funzionamento ? per me sarebbe più semplice l’adattamento vista la poca capacità che ho di programmare con vba

            Perdonami ma mi piace capire quello che leggo , potrebbe servirmi ad imparare.

            Grazie mille :wacko: :bye: :bye:

          • luigi.castoluigi.casto
            Partecipante
              Post totali: 99

              Ho dimenticato una cosa molto importante , come faccio ad ottenere i risultati in ore ?

              Ho provato a formattare le celle ma il risultato è errato

            • AvatarKris_9951
              Partecipante
                Post totali: 226

                Ciao Luigi,

                mi aspettavo che il codice funzionava direttamente sul tuo file…

                Se mi dici che tipo di errore ricevi potrei aiutarti meglio…

                Per quanto riguarda il risultato in ore credo proprio che non si possa ottenere nel modo che vuoi tu ovvero, “14:30” per intendere un totale di 14 ore e 30 minuti in quanto nel momento in cui superi 24 ore il conteggio ripartirebbe da zero perché in formato ore non esiste ad esempio “27:30”.

                Spero di essermi spiegato.

                Il formato ore/minuti puoi mantenerlo fino a quando non superi le 24 ore perché poi ricomincia da 00:00 ed è per questo che ho dovuto convertire in formato decimale.

                Alla fine è la stessa cosa:

                anzichè avere base 60 è a base 100 quindi il quarto d’ora è x,25, la mezz’ora è x,50 ed i 3/4 d’ora è x,75

                • luigi.castoluigi.casto
                  Partecipante
                    Post totali: 99

                    Ciao Kris ,

                    il formato ore esiste anche oltre le 24 , infatti sapevo che raggiunto le ore 24 ritorna a zero ma con la formattazione [hh]:mm;@  si ottiene anche 37:00 per esempio , in questo caso non si ferma alle 24:00

                • luigi.castoluigi.casto
                  Partecipante
                    Post totali: 99

                    http://www.filedropper.com/sommaorelavoro

                     

                    Ciao , ho capito dov’è l’errore che ho fatto io .

                    nei fogli le celle da sommare si trovano nelle colonne LMN (12-13-14), ecco perchè il calcolo era errato,

                    ho provato a cambiare alcuni parametri ma niente ….ho ancora bisogno di aiuto

                    Grazie :unsure:

                  • AvatarKris_9951
                    Partecipante
                      Post totali: 226

                      Non sapevo potesse andare oltre le

                      ore 24:00  👍👍

                      Buono a sapersi 👌

                       

                      Domani gli do un’occhiata e ti aggiorno 👍

                      • luigi.castoluigi.casto
                        Partecipante
                          Post totali: 99

                          Si si è possibile , serve a calcolare la somma delle ore oltre le 24 .

                          Comunque , scusami ancora ma stasera non ci sono proprio , le colonne sono dalla L alla P e sono 5

                          http://www.filedropper.com/sommaorelavoro_1

                          Grazie e buona notte

                      • BySalvBySalv
                        Amministratore del forum
                          Post totali: 884

                          Ciao Luigi, ed un saluto a Kris, Excel come per le Date vede l’orario come numero decimale, quindi non occorre fare la conversione in numeri decimali, perche si superano le 24 ore, basta solo la formattazione delle celle, come indicato da Luigi.

                          credo che ci sia un problema però Luigi indica “Ore previste” ed “Ore effettive”, penso che possa succedere che le “Ore previste” siano maggiori delle “Ore effettive” in questo caso si avrà uno straordinario “Negativo” con il fastidioso problema della visualizzazione delle ore negative con il classico “############” nella cella, tranne se non ha previsto nella formula dello straordinario il valore negativo riportandolo con 0zero.

                          per ovviare a questo basta andare nelle opzioni ed impostare in “Durante il calcolo della cartella di lavoro”, “Usa sistema data 1904”, questo visualizzerà le ore in negativo.

                          come potrai vedere nel foglio che ho aggiunto copiandolo dall’altro file “01-08-2021 (2)”, vedi anche il formato che ho usato per vedere le ore in negativo ed in rosso, come pure vedrai in “N10” una banale somma delle ore

                          ti ho creato una nuova macro che fa tutto il lavoro, questa

                          Sub Tot_Ore()
                          Dim r, c, d, x, y, z, fg, rng, shG As Worksheet, sh As Worksheet
                          
                          Set shG = Worksheets(ActiveSheet.Name)
                          rng = Range("Nomi")
                          shG.Range("B2:F300").ClearContents
                          For y = 1 To UBound(rng)
                            d = rng(y, 1)
                            For x = 1 To Sheets.Count
                              If Sheets(x).Name Like "*-*" Then
                                Set sh = Worksheets(Sheets(x).Name)
                                For z = 2 To sh.Cells(Rows.Count, 1).End(xlUp).Row
                                  If sh.Cells(z, 1) = d Then
                                    shG.Cells(y + 1, 2) = shG.Cells(y + 1, 2) + sh.Cells(z, 12)
                                    shG.Cells(y + 1, 3) = shG.Cells(y + 1, 3) + sh.Cells(z, 13)
                                    shG.Cells(y + 1, 4) = shG.Cells(y + 1, 4) + sh.Cells(z, 14)
                                    shG.Cells(y + 1, 5) = shG.Cells(y + 1, 5) + sh.Cells(z, 15)
                                    shG.Cells(y + 1, 6) = shG.Cells(y + 1, 6) + sh.Cells(z, 16)
                                    Exit For
                                  End If
                                Next z
                              End If
                            Next x
                          Next y
                          End Sub

                          ma attenzione per il calcolo dei dati controlla se il nome del foglio contiene “-” il trattino di separazione per la data per la discriminazione dagli altri fogli, quindi se altri nomi dei fogli non interessati dal calcolo contengono il trattino al limite lo modifichi mettendo un “underscore” trattino basso.

                          per il resto basta usare la formattazione.

                          allego anche il file
                          http://www.filedropper.com/mio-sal

                          se ci sono problemi fai sapere

                          Ciao By Sal (8-D

                          P.S. dimenticavo nel foglio “Totali” la colonna “A” è dinamica cioè puoi eliminare od inserire nuovi nomi per il calcolo, ti calcola solo quelli che inserisci.

                          Ciao By Sal (8-)
                          se ti piace la soluzione aiuta a sostenere il Forum con una DONAZIONE a piacere, Grazie

                          • luigi.castoluigi.casto
                            Partecipante
                              Post totali: 99

                              Ciao sal ,

                              per quanto riguarda i risultati negativi ho risolto con una formula che nel caso di ,appunto, risultato negativo , mi restituisce 0 .

                              Grazie per la dritta  del 1904 , lo conoscevo ma con la formula non mi è servito.

                              Invece per quanto riguarda i nomi dei fogli , nessuno contiene “-” , solo uno il “_” ma non crea fastidio al codice .

                              Vorrei solo fare una preghiera a voi che di vba ne sapete tanto , potreste commentare i codici in modo da imparare anche il funzionamento ed evitare di rompere le scatole ad ogni difficoltà ?

                              per esempio , ho visto molti codici contenenti cicli ma non riesco a capire quale serva a cosa e mi piacerebbe capirlo ….. non so nemmeno cercare un dato in una colonna per poi riscuotere il dato della colonna a dx o sx  , eppure anche in questi codici da voi postati si fa la stessa operazione .

                              Comunque grazie mille per l’aiuto che mi date :yahoo: :bye: :bye:

                          • AvatarKris_9951
                            Partecipante
                              Post totali: 226

                              Ciao Luigi!

                              Ti passo anche l’aggiornamento della mia soluzione.

                              Ho usato la formattazione [hh]:mm;@ che mi ha consentito di imparare una cosa nuova sul formato data/ora :-)

                              Ora ne hai 2 da scegliere perchè c’è anche quella di bySalv ( :bye: :bye: )

                              Fai sapere ( se vuoi ) :good:

                              https://www.dropbox.com/s/uigjju76wdr0uu9/SommaOreLavoro-2.xlsm?dl=0

                              Sub TotaleOre()
                                  Dim wsTotali As Worksheet
                                  Dim matrNomi() As Variant
                                  Dim rngNomi As Range
                                  
                                  Set wsTotali = Sheets("Totali")
                                  Set rngNomi = wsTotali.Range("A1").CurrentRegion.Columns(1).Offset(1, 0)
                                  Set rngNomi = rngNomi.Resize(rngNomi.Rows.Count - 1, 1)
                                  
                                  If wsTotali.Cells(2, 1).Value <> vbNullString Then
                                      If rngNomi.Cells.Count > 1 Then
                                          matrNomi = rngNomi.Value
                                          ReDim Preserve matrNomi(1 To rngNomi.Cells.Count, 1 To 6)
                                      Else
                                          ReDim matrNomi(1 To 1, 1 To 6)
                                          matrNomi(1, 1) = rngNomi.Value
                                      End If
                                      
                                      Call Mod_TotOre.creaMatrice(matrNomi)
                                  
                                      With wsTotali
                                          .Range("A1").CurrentRegion.Offset(1, 0).ClearContents
                                          .Range("A2").Resize(UBound(matrNomi), 6).Value = matrNomi
                                      End With
                                      MsgBox "Aggiornamento effettuato", vbInformation, "Operazione eseguita"
                                  Else
                                      MsgBox "Impossibile procedere!" & Chr(13) & "Il campo nomi non sembra essere popolato correttamente!", vbCritical, "Errore!"
                                  End If
                                  
                                  Set wsTotali = Nothing
                                  Set rngNomi = Nothing
                                  Erase matrNomi
                              End Sub
                              
                              Sub creaMatrice(matrice() As Variant)
                                  Dim ws As Worksheet
                                  Dim x As Long, j As Long, k As Long, y As Long
                                  
                                  For Each ws In ThisWorkbook.Worksheets
                                      If IsDate(ws.Name) Then
                                          x = 2
                                          Do While ws.Cells(x, 1).Value <> vbNullString
                                              For j = LBound(matrice) To UBound(matrice)
                                                  If matrice(j, 1) = ws.Cells(x, 1).Value Then
                                                      y = 12
                                                      For k = 2 To 6
                                                          matrice(j, k) = matrice(j, k) + (ws.Cells(x, y).Value)
                                                          y = y + 1
                                                      Next k
                                                  End If
                                              Next j
                                              x = x + 1
                                          Loop
                                      End If
                                  Next ws
                              End Sub
                            • luigi.castoluigi.casto
                              Partecipante
                                Post totali: 99

                                Ciao e grazie ad entrambe  ,

                                provo subito tutti e due i codici , anche se non ho dubbi che siano entrambe funzionanti alla perfezione .

                                Vi darò riscontro appena finito

                                Grazie ancora per l’impegno :bye:

                              • luigi.castoluigi.casto
                                Partecipante
                                  Post totali: 99

                                  Ciao a tutti ,

                                  i vostri codici funzionano alla perfezione entrambe e ora mi vien voglia di avere 2 file uguali per poter vedere in azione tutti e 2 i codici , Grazie .

                                  Avrei una cosa da chiedere ancora ,

                                  come detto , la formattazione ora oltre le 24 è fattibile ma non riesco a farlo con label o textbox  , per caso non è possibile o sono io a non riuscirci ?

                                  per esempio vorrei : me.label1.caption=range[B8].value

                                  [B8] contiene : 32:00

                                  al massimo sono riuscito ad ottenere che label1 sia = 32 ma non formato ora, con me.label1.caption=range[B8].value*24

                                  Per caso avete una soluzione anche a questo ? :mail: :bye: :bye:

                                • BySalvBySalv
                                  Amministratore del forum
                                    Post totali: 884

                                    Ciao Luigi, premesso che una listbox un combobox oppure un testbox restituiscono sempre testo e non numeri, quindi resta inutile la formattazione se non per altri scopi.
                                    ti inserisco un esempio di un listbox su un foglio, come vedi ci sono ore sul foglio che superano le 24 ore ed il listbox le vede come sono scritte, io gli ho dato solo il range dei dati “A1:F10” in “ListFillRange” che è diverso da quello su una userform che usa “RowSource” ma ha la stessa funzione.
                                    ti ho anche inserito le proprietà che ho assegnato alla Listbox in modo che puoi vedere come ho impostato la listbox per le colonne e la larghezza delle stesse.

                                    in poche parole la listbox riporta il testo che vede sul foglio per come sono formattati.

                                    In risposta alla tua richiesta sul VBA, devi considerare prima i dati che possiedi, poi come puoi prendere o gestire i dati e dopo di questo realizzare le macro.

                                    nel tuo file hai un foglio “Totali” in cui ci sono dei nominativi e poi in altri fogli dove ci sono i nominativi più le ore.

                                    la prima cosa partire dal foglio “Totali” mettere tutti i nominativi della colonna “A” in una matrice “Rng” ma potrei anche prenderli scorrendo le celle, pulire i dati delle ore vecchie e creare il primo ciclo che scorre i nomi uno alla volta, un secondo ciclo per scorrere tutti i fogli presenti nel file, con un filtraggio dei soli fogli interessati, nel tuo caso la discriminante per i fogli con i dati era il “-” trattino presente nella data, ma poteva anche essere un altro esempio che il foglio cominciasse con “Orario”, a questo punto avendo selezionato il foglio interessato creo un terzo ciclo per la ricerca del Nome nella colonna “A”, perche so che il nominativo si trova nella colonna “A”, ma poteva essere anche un altra qualsiasi cosa in altre colonne.
                                    a questo punto se è presente il nominativo si fermerà sulla riga del nome e prendo i dati che mi interessano dalle colonne interessate e le copio nella riga del nominativo di partenza.
                                    come vedi io non uso Offset() ma uso Cells(Riga, colonna) che per me è più diretto e sicuro, so dove prendo i dati e dove li devo trascrivere.
                                    nel caso mentre li trascrivo faccio anche la somma dei dati precedenti, non li metto in memoria sommandoli per poi trascrivere, con Offset dovrei calcolarmi lo spostamento.
                                    in definitiva scorrendo singolarmente i nomi del foglio di partenza scorro i fogli trovo il nome e trascrivo sommando i dati, essendo numeri non devo fare nessuna conversione, ci pensa il formato a farmi vedere le ore giuste complessive.

                                    Ciao By Sal (8-D

                                    Ciao By Sal (8-)
                                    se ti piace la soluzione aiuta a sostenere il Forum con una DONAZIONE a piacere, Grazie

                                  • luigi.castoluigi.casto
                                    Partecipante
                                      Post totali: 99

                                      Ciao sal ,

                                      grazie mille anche per la spiegazione inserita , potrei scaricarlo per esaminare il codice ?

                                      con la tua spiegazione potrei valutare passo passo e comprenderne il funzionamento.

                                      Qui non trovo il link

                                    • BySalvBySalv
                                      Amministratore del forum
                                        Post totali: 884

                                        Ciao scusa Luigi mi sono dimenticato di allegarlo, pensavo che l’immagine poteva essere esaustiva.

                                        ecco il file
                                        http://www.filedropper.com/mio-sal_1

                                        Ciao By sal (8-D

                                        Ciao By Sal (8-)
                                        se ti piace la soluzione aiuta a sostenere il Forum con una DONAZIONE a piacere, Grazie

                                      • luigi.castoluigi.casto
                                        Partecipante
                                          Post totali: 99

                                          Ciao sal , ho scaricato il file e visto la proprietà ListFillRange che però non vedo nelle label e non trovo una soluzione uguale per esse.

                                          Forse è meglio  sostituire le label con una listbox ?

                                        • BySalvBySalv
                                          Amministratore del forum
                                            Post totali: 884

                                            Ciao Luigi, le Label sono etichette, la Listbox una lista di elementi sono 2 cose differenti.

                                            se vuoi riportare un dato dal foglio alla textbox o la Label i modi sono differenti.

                                            nel textbox basta scrivere “Textbox1 = Range(“A1″)”
                                            probabile che ti riporti il numero seriale delle ore cioè 0,xxxxx
                                            nel caso devi usare il formato
                                            Textbox1 = format(range(“A1”), “hh:mm”)
                                            se non va nemmeno con format scrivi “Textbox1 = Cdate(Range(“A1″))”

                                            nella Label invece devi scrivere “Label.Caption = “Range(“A1″)”

                                            non so dove devi scrivere questi dati Userform?, Foglio?, forse se mandi un esempio sarebbe meglio.

                                            Ciao By Sal (8-D

                                            Ciao By Sal (8-)
                                            se ti piace la soluzione aiuta a sostenere il Forum con una DONAZIONE a piacere, Grazie

                                          • luigi.castoluigi.casto
                                            Partecipante
                                              Post totali: 99

                                              Ciao sal ,

                                              ho già provato nel modo da te descritto ma nella label viene trascritta la data in numero seriale e il format non funziona .

                                              Ho optato per sostituire le label con una listbox che comunque è contenuta in una userform .

                                              Molte grazie per il tuo intervento , sempre gentile e disponibile .

                                              Posso farti una domanda a cui non so se hai la possibilità di rispondere …

                                              sid frequenta ancora questo forum ? è molto che non lo sento e siccome è stato molto cordiale e disponibile mi piacerebbe salutarlo .

                                              Buona serata

                                            • AvatarKris_9951
                                              Partecipante
                                                Post totali: 226

                                                Ciao Luigi!

                                                Posso salutartelo io 👍👍

                                                Sid è un grande!!! 😉😉

                                              • luigi.castoluigi.casto
                                                Partecipante
                                                  Post totali: 99

                                                  Grazie Kris ,

                                                  gli ho talmente rotto le scatole che si ricorderà sicuramente di me :whistle: :yahoo:

                                                  ciao

                                                • sidsid
                                                  Moderatore
                                                    Post totali: 754

                                                    Saluti a tutti :bye:

                                                  • luigi.castoluigi.casto
                                                    Partecipante
                                                      Post totali: 99

                                                      Ciao sid , bello risentirti

                                                      :bye:

                                                    • sidsid
                                                      Moderatore
                                                        Post totali: 754

                                                        Ciao Luigi!

                                                        Posso salutartelo io 👍👍

                                                        Sid è un grande!!! 😉😉

                                                        Troppo buono :-)

                                                        • AvatarKris_9951
                                                          Partecipante
                                                            Post totali: 226

                                                            Troppo vero!!

                                                            😉👍👍

                                                      Visualizzazione 21 filoni di risposte
                                                      • Devi essere connesso per rispondere a questo topic.