Estrarre un intervallo di celle (dal giorno 1 al giorno n) avendo come input la data n da un range di celle la cui prima colonna contiene date in sequenza.

Question

Un saluto a tutti. Excel 2007 e 2010 tutti gli update su W7 Professional tutti gli SP e update.

Nel foglio Sheet1 ho la colonna B che ospita delle date in sequenza a partire dalla riga 11. In questa colonna accodo la data n (cioè la data di domani) ogni giorno. Le colonne da C a N contengono dei dati quotidiani che non sono rilevanti per la domanda.

Nel foglio Sheet2 ho un semplice grafico a linee che presenta i dati di sheet1 dal giorno 1 al giorno n. In questo foglio, sotto il grafico, ho posizionato la tabella necessaria come sorgente dati per il grafico visto che il foglio va inviato in visione via email a diversi soggetti.

Quello che faccio ora è copiare a mano da Sheet1 a sheet2 i dati che vanno dal 1º del mese alla data n.

Quello che dovrei ottenere sarebbe di acquisire "automaticamente" il range dal 1º del mese alla data n sulla base della data n stessa. Una volta ottenutolo, via vba, selezionare e copiare il range su sheet1 e incollarlo su sheet2.

Via vba sarei tentato di eseguire su sheet1 un ciclo all'indietro partendo dalla cella della data n (di cui conosco le coordinate senza necessità di cercarle) per trovare il 1º giorno del mese e selezionare il relativo range. 

Scrivo una sorta di pseudocodice sperando che risulti maggiormente comprensibile:

data n = 05/01/2017; 

coordinate Row,Col di data n = (1173, 2);

range da selezionare e copiare = range(cells(1173-Day(data n) + 1) ,2),cells(1173,14)).Select

Copia, incolla ecc

Via formula invece non ho molte idee su come procedere alla determinazione della posizione del range da ottenenre né certezze sul fatto che sia conveniente farlo.

Ecco infine le domande:

L'approccio vba di cui sopra puó andare o vi sono best practices sull'argomento che potrebbero fare al caso mio?

Ho scoperto che se nascondo le celle che fanno da sorgente dati al grafico nell'area del grafico non visualizzo nulla. Il foglio che viene inviato per email è ovviamente protetto ma vedere la tabella dati sotto il grafico non è il massimo. Per il momento ho risolto cambiando il colore del carattere da automatico a bianco ma gradirei adottare una soluzione più elegante. Qualche suggerimento?

Ringrazio

Nicholas

Answer 1

Ciao Nicholas,

Excel 2007 e 2010 tutti gli update su W7 Professional tutti gli SP e update.

Nel foglio Sheet1 ho la colonna B che ospita delle date in sequenza a partire dalla riga 11. In questa colonna accodo la data n (cioè la data di domani) ogni giorno. Le colonne da C a N contengono dei dati quotidiani che non sono rilevanti per la domanda.

Nel foglio Sheet2 ho un semplice grafico a linee che presenta i dati di sheet1 dal giorno 1 al giorno n. In questo foglio, sotto il grafico, ho posizionato la tabella necessaria come sorgente dati per il grafico visto che il foglio va inviato in visione via email a diversi soggetti.

Quello che faccio ora è copiare a mano da Sheet1 a sheet2 i dati che vanno dal 1º del mese alla data n.

Quello che dovrei ottenere sarebbe di acquisire "automaticamente" il range dal 1º del mese alla data n sulla base della data n stessa. Una volta ottenutolo, via vba, selezionare e copiare il range su sheet1 e incollarlo su sheet2.

Via vba sarei tentato di eseguire su sheet1 un ciclo all'indietro partendo dalla cella della data n (di cui conosco le coordinate senza necessità di cercarle) per trovare il 1º giorno del mese e selezionare il relativo range. 

Scrivo una sorta di pseudocodice sperando che risulti maggiormente comprensibile:

data n = 05/01/2017; 

coordinate Row,Col di data n = (1173, 2);

range da selezionare e copiare = range(cells(1173-Day(data n) + 1) ,2),cells(1173,14)).Select

Copia, incolla ecc

Via formula invece non ho molte idee su come procedere alla determinazione della posizione del range da ottenenre né certezze sul fatto che sia conveniente farlo.

Ecco infine le domande:

L'approccio vba di cui sopra puó andare o vi sono best practices sull'argomento che potrebbero fare al caso mio?

Ho scoperto che se nascondo le celle che fanno da sorgente dati al grafico nell'area del grafico non visualizzo nulla. Il foglio che viene inviato per email è ovviamente protetto ma vedere la tabella dati sotto il grafico non è il massimo. Per il momento ho risolto cambiando il colore del carattere da automatico a bianco ma gradirei adottare una soluzione più elegante. Qualche suggerimento?

Per individuare i dati,  copiarli sul secondo foglio, nasconderli e rendere il grafico visibile anche con i  suoi dati nascosti,  prova qualcosa del genere:

• Alt+F11 per aprire l'editor di VBA
• Alt+IMper inserire un nuovo modulo di codice
• Nel nuovo modulo vuoto, incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim WB As Workbook

    Dim srcSH As Worksheet, destSH As Worksheet

    Dim rngData As Range, srcRng As Range, destRng As Range

    Dim dDate As Date

    Dim iGiorni As Long, LRow As Long

    Const sFoglioDati As String = "Sheet1"                  '<<=== Modifica

    Const sFoglioGrafico As String = "Sheet2"              '<<=== Modifica

    Const sCellaDestinazione As String = "B25"            '<<=== Modifica

    Set WB = ThisWorkbook

    With WB

        Set srcSH = .Sheets(sFoglioDati)

        Set destSH = .Sheets(sFoglioGrafico)

    End With

    dDate = Date

    With srcSH

        Set rngData = .Range("B:B").Find( _

                      What:=(dDate), _

                      LookIn:=xlValues, _

                      LookAt:=xlWhole, _

                      SearchOrder:=xlNext)

        If Not rngData Is Nothing Then

            With rngData

                iGiorni = Day(.Value) + 1

                Set srcRng = .Offset(-iGiorni + 2).Resize(iGiorni - 1, 2)

            End With

            Set destRng = destSH.Range(sCellaDestinazione)

            srcRng.Copy Destination:=destRng

            destRng.EntireRow.Hidden = True

            destSH.ChartObjects(1).Chart.PlotVisibleOnly = False

        End If

    End With

End Sub

'<<========= 

• Alt+Q per chiudere l'editor di VBA e tornare a Excel
• Salva il file con l’estensione xlsm
• Alt+F8 per aprire  la finestra di gestione delle macro
• Seleziona Tester | Esegui

Nota che il commando 

      destSH.ChartObjects(1).Chart.PlotVisibleOnly = False

rappresenta un modo automatico per seguire i passi manuali descritti nel seguente articolo di Support:

https://support.office.com/it-it/article/Visualizzare-dati-nascosti-e-celle-vuote-in-un-grafico-a1ee6f0c-192f-4248-abeb-9ca49cb92274?ui=it-IT&rs=it-IT&ad=IT

Potresti scaricare il mio file di prova Nicholas20170124.xlsm a:

https://www.dropbox.com/s/j9kmuix05nh4kyk/Nicholas20170124.xlsm?dl=0

Vorrei approfittare per chiederti gentilmente di contrassegnare le risposte da Fratello Paolo e me alla tua domanda precedente (https://answers.microsoft.com/it-it/msoffice/forum/msoffice_excel-mso_winother/formula-per-contare-celle-che-contengono-una/65f0e626-f9c1-4f74-afe1-815ebdae5aaa) come Risposta preferita. In questo modo, tu aiuterai anche coloro che potessero cercare soluzioni ai problemi simili negli archivi della Community.

  

===

Regards,

Norman

Answer 2

Fermate le macchine!!!

Sostituendo xlFormulas a xlValues nel parametro LookIn la find () funziona. La cosa mi lascia perplesso poiché la data nella cella viene scritta direttamente e non deriva dalla valutazione di formule.

Answer 3

Grazie per le risposte. Visualizzazione grafico da sorgente dati nascosta ok, risolto.

Ho ancora problemi sull'utilizzo della range.find ()...

La colonna in cui cercare le date  è  la C quindi adesso la find risulta essere così 

Set rngData = .Range ("C:C").Find(what:=dDate, LookIn:=xlValues,LookAt:=xlWhole, SearchOrder:=xlNext)

Eseguendo la funzione purtroppo rngData rimane sempre Nothing e quindi non viene costruito il range che mi interessa poi copiare.

La data é  presente nella colonna C visualizzata nella forma 29-gen-2017.

La variabile dDate prima della find () viene valorizzata correttamente in 29/01/2017

Il sistema è impostato in italiano e la data breve è  nel formato gg/MM/yyyy.

Answer 4

Ciao Nicholas,

Se desideri che i dati del grafico non dovessero essere visibili al ricevente, forse potresti anche prendere in considerazione l'invio di una versione PDF del foglio di lavoro.

===

Regards,

Norman