Excel - Attivare la cella di riferimento

[Flash30005]

Ciao Anthony
Ho un foglio (Internet) che con la WebQuery prende i dati
Nella colonna A2:A100 di questo foglio ho la data in ordine decrescente
In un secondo foglio (VerificaCol) nella cella E26 ho il riferimento (quando è aggiornato ha la stessa data del foglio Internet cella A2.
Ho un pulsante (Precedente) che attiva la seguente macro

Codice: Seleziona tutto

Call SProtez
 Sheets("Internet").Select
 ActiveSheet.Cells(Selection.Row, 1).Select
 Selection.Offset(1, 0).Select
 Selection.Copy
 Sheets("VerificaCol").Select
 Range("F26").Select
 ActiveSheet.Paste
 Call Protez

Ed è tutto ok.
Se io digito manualmente la data in E26 (del foglio VerificaCol) ottengo i dati relativi alla data prescelta ma
se ripremo di nuovo il pulsante (Precedente) la macro mi fornisce il valore della cella in A3 (di Internet) ripresentandola in E26 (di VerificaCol) in quanto la cella attiva precedente era A2.
Vorrei con un codice (credo sia semplice ma non riesco a trovarlo) rendere attiva la cella sulla colonna A corrispondente al valore della cella di E26 in maniera tale che al successivo comando Precedente indietreggi (scenda di una riga) rispetto alla data di riferimento in E26 (di VerificaCol)
Quale codice mettere all'inizio della macro per fare questo?

[Flash30005]

Ciao Anthony
Ho modificato la macro in questa maniera e funziona

Codice: Seleziona tutto

Sub Preced()
Call SProtez                     '(macro sprotegge i fogli)
RicIn:
DateI = Worksheets("Internet").Range("A2").Value
Sheets("Internet").Select
Range("A2").Select
DateV = Worksheets("VerificaCol").Range("E26").Value
ContRic:
If DateI = "" Then GoTo RicIn
If DateI <> DateV Then
 Sheets("Internet").Select
 ActiveSheet.Cells(Selection.Row, 1).Select
 Selection.Offset(1, 0).Select
 DateI = ActiveCell.Value
 GoTo ContRic
 Else
  Sheets("Internet").Select
 ActiveSheet.Cells(Selection.Row, 1).Select
 Selection.Offset(1, 0).Select
 Selection.Copy
 Sheets("VerificaCol").Select
 Range("F26").Select
 ActiveSheet.Paste
 Range("H25").Select
Call Protez                         '(macro riprotegge i fogli)
End If
End Sub

ma con tutti questi rinvii mi sembra molto farraginosa
Sicuramente tu troverai una soluzione più lineare

[Anthony47]

Come principio, macro che funziona e' buona macro...
Solo a uso didattico, se l' obiettivo e' trovare in quale riga di col A su "Internet" si trova il valore contenuto su "VerificaCol" E26 io avrei fatto come segue

Ipotesi 1

Codice: Seleziona tutto

Datatarg =Worksheets("VerificaCol").range("E26").value
Sheets("Internet").select
For each Cella in Range("A1:A100")
If Cella.value=DataTarg then
Cella.offset(1,0).select
Exit For
Next Cella

Da questa macro esci con selezionato il foglio Internet, la cella successiva alla prima trovata in col A col valore pari a E26.

Ipotesi 2:
in una cella libera di VerificaCol, es E27, scrivi la formula

Codice: Seleziona tutto

=CONFRONTA(E26;Internet!A:A)

Nel vba inserisci

Codice: Seleziona tutto

ScartoV=Worksheets("VerificaCol").Range("E27").value
sheets("Internet").select
Range("A1").offset(ScartoV,0).select

Questa te la propongo per farti prendere confidenza col concetto di Offset=Scarto

Ciao.

[Flash30005]

Provata ipotesi 1 tutto ok
ma la seconda è superlativa

Ipotesi 2:
in una cella libera di VerificaCol, es E27, scrivi la formula

Codice: Seleziona tutto

=CONFRONTA(E26;Internet!A:A)

Nel vba inserisci

Codice: Seleziona tutto

ScartoV=Worksheets("VerificaCol").Range("E27").value
sheets("Internet").select
Range("A1").offset(ScartoV,0).select

Questa te la propongo per farti prendere confidenza col concetto di Offset=Scarto

Ho corretto solo con

Codice: Seleziona tutto

=CONFRONTA(E26;Internet!A:A;0)

"corrispondenza VERO, FALSO"
altrimenti mi dava il numero delle colonne in A (100)

e siccome l'appetito vien mangiando...
so che esiste il controllo sulla variazione del valore di una cella ma cerca e ricerca non riesco a trovarlo

Domanda:
Se volessi che la macro all'ipotesi 2 funzioni alla variazione del valore della data in E26 (dopo aver digitato la data e premuto Invio si attivi la macro) quale istruzione dovrò dare?

P.s. Nell'help molto fornito non sempre si riesce a trovare tutto e subito e quando non si trova l'unico vantaggio è quello di imparare nuove cose (si legge tutto sperando che sia l'argomento risolutore ed invece... )

[Flash30005]

Aggiungo ho inserito una data manuale che non era tra i valori previsti nella colonna A del foglio Internet e la macro va in errore, si può mettere un controllo che prende il campo più prossimo a quella data?

[Flash30005]

Dopo una breve dormita si è accesa la lampadina

avevo trovato il codice di aggiornamento foglio ma non l'avevo messo nel foglio che aggiornavo (VerificaCol > E26)

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "cambiato"
....
      end sub

Rimane il probelma della formula Confronta che non trovando una eventuale data digitata manualmente fornisce Errore e quindi tutti i riferimenti collegati al campo data vanno in "tilt"

[Anthony47]

Noto adesso che le date sono in ordine decrescente, questo rende la formula Confronta inutilizzabile (sarebbe andata bene la mia versione se le date fossero state in ordine crescente).
Sostituiscila con questa:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--((Internet!B1:B1000)>=E26))

Ciao.

[Flash30005]

Noto adesso che le date sono in ordine decrescente, questo rende la formula Confronta inutilizzabile (sarebbe andata bene la mia versione se le date fossero state in ordine crescente).
Sostituiscila con questa:

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--((Internet!B1:B1000)>=E26))

Ciao.

Beh quale magia tu abbia fatto ancora non l'ho capita...
la formula è corretta ma la colonna di riferimento non è B ma A, altrimenti mi fornisce sempre valore 1, giusto?
Allora al momento che si inserisce una data sbagliata il "codice magico" Matr.Somma.prodotto (ho cercato di capirlo ma invano per come l'hai strutturato) funziona dando un numero intero e non più errore il problema è che sono i miei riferimenti basati sulla Cella E26 avendo usato il cerca.vert
per esempio la casella B25 ha il seguente codice

Codice: Seleziona tutto

=CERCA.VERT(E26;Internet!A2:H105;2;FALSO)

non trovando la data di riferimento B25 otterrò errore come pure C25

Codice: Seleziona tutto

=CERCA.VERT(E26;Internet!A2:H105;3;FALSO)

purtroppo la macro Vba è sotto pulsante Precedente e non sostituirà il valore della data corretto fino a che non si preme il pulsante Precedente o Successivo e potrei anche accontentarmi lasciando questa piccola "disfunzione".

Avevo pensato di mettere la macro da te postata:

Codice: Seleziona tutto

ScartoV = Worksheets("VerificaCol").Range("E27").Value
Sheets("Internet").Select
Range("A1").Offset(ScartoV, 0).Select
 Selection.Copy
 Sheets("VerificaCol").Select
 Range("F26").Select
 ActiveSheet.Paste

anche nell'aggiornamento del foglio ma va in loop
una parte della colonna in A (foglio Internet) è questa:

Codice: Seleziona tutto

Data
27/09/2008
25/09/2008
23/09/2008
20/09/2008
18/09/2008
16/09/2008
13/09/2008
11/09/2008
09/09/2008
06/09/2008
04/09/2008
02/09/2008
30/08/2008
28/08/2008
26/08/2008
23/08/2008
21/08/2008
19/08/2008
16/08/2008
14/08/2008
12/08/2008
09/08/2008


se io digito 15/08/2008 la tua formula mi fornisce 20 che corrisponde al 16/08/2008 basterebbe incollare il 16/08/2008 in E26 (che la macro già fa di per sé) e non capisco il loop, forse perché l'aggiornamento arriva prima del calcolo della formula in E27 (con valore iniziale 2) infatti durante il loop quel valore (2 in E27) non cambia.
Cosa mi consigli di usare un timer nella macro aggiornamento foglio? Per fare in modo che la formula in E27 dia valore 20 e dopo il ritardo di 1 secondo la macro-aggiornamento sostituisca il valore in E26?

[Flash30005]

Sto facendo delle prove nel frattempo e ho visto che utilizzando in B25 (e B26) la funzione Scarto invece del Cerca.Verticale :

Codice: Seleziona tutto

=SCARTO(Internet!A1;E27-1;1)

i riferimenti sono corretti e si riferiscono alla data del 16/08/2008, l'unica cosa che rimane errata è la data in E26 (15/08/2008) che non esiste.
Quindi ora Anthony un'altra magia: è possibile controllare l'aggiornamento di una determinata cella (es. la E27)?
Se fosse possibile potrei mettere una macro su quell'aggiornamento che mi va a rimpiazzare la data In E26 e avrei proprio finito in bellezza, grazie a te

[Anthony47]

Il fatto e' che adesso non ti seguo piu'...
All' inizio sembrava che volessi individuare, nell' elenco di date, dove andava posizionata quella impostata in E26, adesso invece capisco che E26 e' la chiave per estrarre dati da una tabella ed E27 (o dove abbiamo messo la formula piu' o meno magica) e' solo una cella di servizio.
Prima di lanciarmi su un giro tortuoso, non puoi mettere su E26 una convalida da elenco, con l' elenco di Internet col A? In questo modo su E26 potrai solo inserire/scegliere una data valida.

Ciao.

[Flash30005]

Il fatto e' che adesso non ti seguo piu'...
All' inizio sembrava che volessi individuare, nell' elenco di date, dove andava posizionata quella impostata in E26, adesso invece capisco che E26 e' la chiave per estrarre dati da una tabella ed E27 (o dove abbiamo messo la formula piu' o meno magica) e' solo una cella di servizio.
Prima di lanciarmi su un giro tortuoso, non puoi mettere su E26 una convalida da elenco, con l' elenco di Internet col A? In questo modo su E26 potrai solo inserire/scegliere una data valida.

Ciao.

Si capisco che per essere chiaro sono stato prolisso e ti ho confuso le idee ma ricapitolando
tutto funziona perfettamente bene:
1) la formula in E27 (cella di servizio) dà sempre un numero valido e non va più in errore
2) i miei riferimenti agganciati alla cella di servizio E27 ora forniscono dati sempre giusti

Rimane solo il fatto che digitando a mano la data potrebbe non essere uguale a una di quelle dell'elenco
A me andrebbe benissimo poter scegliere solo una data valida da elenco (col A) ma come?

In attesa di tue risposte stavo giusto smanettando sulla cella E26 (tasto dx mouse) e ho provato le uniche voci che pensando fossero utili del tipo "Aggiungi controllo cella" e "Crea Elenco" ma non ho ottenuto nulla utilizzandole (quest'ultima mi ha stravolto la formattazione di tutte le celle E26,F26,G26,H26 che sono originariamente unite).
Mi guidi ancora una volta?
Grazie Anthony

[Flash30005]

Credo di esserci riuscito utilizzando la tua descrizione convalida da elenco con l'help.
La casella ora ha un menu di date corrispondenti alla colonna A dell'altro foglio, in caso di digitazione data errata va in errore e non fa più utilizzare i pulsanti Precedente e Successivo ma poco importa ho messo come opzione errore informativo e quindi tutto ritorna come prima.
Penso che può essere anche questa una soluzione valida.
Grazie Anthony

Non sono riuscito a capire 2 cose
1) la funzione della formula nella cella di servizio come è stata impostata da te

Codice: Seleziona tutto

=MATR.SOMMA.PRODOTTO(--((Internet!A2:A105)>=E26))

i segni -- per cosa stanno pur avendola riprovata exnovo il box mi chiedeva matrice dopo matrice, boh!

2) il perché la macro (funzionante per i pulsanti) messa in qualsiasi tipologia di aggiornamento del foglio:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
ScartoV = Worksheets("VerificaCol").Range("E27").Value
Sheets("Internet").Select
Range("A1").Offset(ScartoV, 0).Select   << Pulsante Preced = Offset(ScartoV+1,0) - >> Pulsante                                                              Success = Offset(ScartoV -1,0)
Selection.Copy
Sheets("VerificaCol").Select
Range("E26").Select
ActiveSheet.Paste
end sub

mi andasse in errore Runtime 1004 alla riga

Codice: Seleziona tutto

Range("A1".Offset(ScartoV,0).Select

perché se avesse funzionato questa macro la cella E26 (con data non prevista) si sarebbe aggiornata da sola con il valore trovato nella colonna A del foglio Internet

[Anthony47]

Scusa se mi sono limitato a dire cosa fare (mettere su E26 una convalida da elenco) senza anche indicare come farlo. Mi pare che hai risolto, bene cosi'.
Per quanto riguarda le domande:
a) il "--" e' una doppia negazione, nella formula serve a trasformare i Vero/Falso resi dal confronto ">=" in 1/0 per sommarli.

b) per l' errore della macro, IMMAGINO che la parte "<< Pulsante Preced = Offset(ScartoV+1,0) - >> Pulsante Success = Offset(ScartoV -1,0)" non sia presente nel codice altrimenti avresti un errore bloccante di sintassi.
Meno male che va in errore altrimenti sarebbe andata in loop visto che la macro produce un "change" in E26 che fa ripartire la stessa macro.
Modifica in questa:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$26" then Exit Sub        'ESCI se la modifica non e' su E26
Application.EnableEvents = False
ScartoV = Worksheets("Foglio2").Range("E27").Value
Sheets("Internet").Range("A1").Offset(ScartoV, 0).Copy Destination:=Range("E26")
Application.EnableEvents = True
End Sub

Application.EnableEvents = False blocca l' identificazione di altri eventi fino alla fine della sub, evitando il loop nel momento della copia in E26. La macro viene eseguita per intero solo se la modifica ha riguardato E26, perche' credo sia questo l' uso che ne vuoi fare; se non ti interesa questa logica (la verifica di E26) togli l' istruzione If.

Ciao, chissa' se ora quadra tutto.

[Flash30005]

Scusa se mi sono limitato a dire cosa fare (mettere su E26 una convalida da elenco) senza anche indicare come farlo...

A volte basta un imput

a) il "--" e' una doppia negazione, nella formula serve a trasformare i Vero/Falso resi dal confronto ">=" in 1/0 per sommarli.

Eccola la tua magia!
La studierò facendo pratica

b) per l' errore della macro, IMMAGINO che la parte [i]"<< Pulsante Preced =...
non sia presente nel codice altrimenti avresti un errore bloccante di sintassi.

Chiaramente no, l'avevo messa qui solo come nota, ma esisteva un errore nel codice Vba da quanto ho capito quel

Codice: Seleziona tutto

Range("A1").Offset(ScartoV.....

non è "digerito" nei "Change Worksheet" e dall'Help avevo trovato questo codice funzionante

Codice: Seleziona tutto

ActiveCell.Offset(rowOffset:=ScartoV, columnOffset:=0).Activate

ma ho preferito usare il doppio click del mouse che rilanciava ad una subroutine in questa maniera

Codice: Seleziona tutto

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Call AggData
End Sub

Codice: Seleziona tutto

Sub AggData()
ScartoV = Worksheets("VerificaCol").Range("E27").Value
Sheets("Internet").Select
Range("A1").Offset(ScartoV, 0).Select
Selection.Copy
Sheets("VerificaCol").Select
Range("F26").Select
ActiveSheet.Paste
End Sub

Meno male che va in errore altrimenti sarebbe andata in loop visto che la macro produce un "change" in E26 che fa ripartire la stessa macro.

Già! Per fortuna che sapevo usare il Ctrl+Pause

Modifica in questa:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$26" then Exit Sub 'ESCI se la modifica non e' su E26
Application.EnableEvents = False
ScartoV = Worksheets("Foglio2").Range("E27").Value
Sheets("Internet").Range("A1").Offset(ScartoV, 0).Copy Destination:=Range("E26")
Application.EnableEvents = True
End Sub

Application.EnableEvents = False blocca l' identificazione di altri eventi fino alla fine della sub, evitando il loop nel momento della copia in E26. La macro viene eseguita per intero solo se la modifica ha riguardato E26, perche' credo sia questo l' uso che ne vuoi fare; se non ti interesa questa logica (la verifica di E26) togli l' istruzione If.
Ciao, chissa' se ora quadra tutto.

QUADRA! QUADRA! Era proprio quello che cercavo e chiesto in un mio post (il "controllo" di aggiornamento di una cella).
Adesso è perfetto! Ho lasciato anche la Convadila Elenco per la comodità di avere tutte le date previste.
Grazie a te anche se il mio utilizzo è solo per hobby ho imparato molte cose che, l'esperienza di vita mi ha insegnato, tornano sempre utili...
Ciao e grazie di nuovo

P.s. per quel che mi riguarda, ora, ti lascerò "tranquillo" per un po'