come superare il limite del primo valore di CERCA.VERT

[renatoelba]

Ciao a tutti, sono nuovo qui.
Ho bisogno di effettuare una ricerca su più colonne di una tabella, in cui la prima colonna contiene dati non univoci.
Su una seconda tabella devo cercare un dato valore della colonna A, e quindi, sulla stessa riga raffrontare altri valori con quelli alle colonne B e C. Quindi restituirmi un valore X se i raffronti hanno esito positivo, 0 se lo hanno negativo. CERCA.VERT si ferma alla prima riga in cui trova, nella prima colonna, il valore da cercare. Invece io vorrei che, in caso di esito negativo sul raffronto operato sulle colonne B e C, il controllo vada avanti a cercare altri valori sulla colonna A.
Il caso specifico è il seguente:

voglio fare un planning delle camere di un hotel a partire da un elenco di prenotazioni.

Nell'elenco ho 3 colonne, con A "Camera", B "data di arrivo", C "data di partenza";

In un altra tabella ho 366 colonne, di cui la prima contiene il numero della camera, e tot righe, corrispondenti al numero di camere oltre quella dell'intestazione. Sulle colonne dalla seconda in avanti i giorni dell'anno.

Poniamo che nella seconda tabella io abbia, come prima camera, la 202, vorrei cercare, per ogni cella corrispondente ad un giorno dell'anno, se nella tabella delle prenotazioni ce n'è una relativa alla stessa camera (quindi con valore 202 sulla prima colonna) per cui quel dato giorno dell'anno ricade tra il giorno di arrivo (valore in colonna B della prima tabella) ed il giorno di parenza (valore in colonna C della prima tabella).

Ho provato con la funzione SE con annidiati due CERCA.VERT, ma la funzione mi cerca solo la prima riga relativa alla camera interessata, quindi si ferma. Io vorrei andare oltre. Come dire che se il risultato è negativo andrebbe fatto un altro CERCA.VERT su una matrice che parte dalla riga più in basso, e così via.

Grazie anticipatamente a tutti

[Marius44]

Ciao Renato
per non far "costruire" i fogli a chi volesse aiutarti (magari commettendo qualche errore di struttura) perchè non provi ad allegare il tuo file?

Ciao,
Mario

[renatoelba]

Posso allegarlo qui o devo pubblicarlo?
Grazie

[alfrimpa]

Ciao Renato

Il file puoi allegarlo con un sito di file sharing tipo Filedropper o Dropbox; ci carichi il file e ti verrà restituito un link che ciopierai qui sul forum.

[renatoelba]

https://www.dropbox.com/s/hf4p29cgfncim ... g.ods?dl=0

E' un esempio su file di calc open office, poiché a casa non ho MS office, ma mi serve per lavoro in ufficio dove invece devo farlo girare su Excel.

Spiego di nuovo cosa deve fare il foglio, con i riferimenti al mo esempio: per ogni camera i in colonna E deve cercare le corrispondenti righe sulla matrice A2:C9 e confrontare il valore E1 (F1, G1, andando avanti con i giorni ecc...) con quelli delle colonne B e C, per cui il valore deve esservi ricompreso (>= Bi e <Ci) e restituire un valore o vero o falso (o qualunque altra variabile booleana).
Detta diversamente: Per ogni casella ji della matrice del planning (F2:BQ12) devono essere verificate 3 condizioni, una di uguaglianza del valore della cella Ei, una di >= del valore della cella Ji e una di < sul valore della cella Ji, rispettivamente sulle colonne A, B e C di ogni riga della matrice di sinistra; per resituire vero o falso.
Immagino ci voglia un ciclo Visual Basic, ho trovato qualcosa ma sono proprio a zero e non conosco la sintassi, mentre ho urgenza di risolvere per il mio lavoro...

[alfrimpa]

Ciao Renato

Non so se ho capito bene il tuo quesito ma ti propongo questo file

http://www.filedropper.com/renatoelba

dove ho inserito la function scritta in VBA che vedi sotto

Codice: Seleziona tutto

Function TrovaPrenotazioni(camera, giorno)
Dim rng As Range
Dim da As Date
Dim a As Date
Application.Volatile
With Sheets("Foglio1").Range("A:A")
    Set rng = .Find(What:=camera, _
               After:=.Cells(.Cells.Count), _
               LookIn:=xlValues, _
               LookAt:=xlWhole, _
               SearchOrder:=xlByRows, _
               SearchDirection:=xlNext, _
               MatchCase:=False)
    If Not rng Is Nothing Then
        da = rng.Offset(0, 1).Value
        a = rng.Offset(0, 2).Value
        If giorno >= da And giorno <= a Then
            TrovaPrenotazioni = "Prenotata"
            Else
            TrovaPrenotazioni = ""
        End If
    End If
End With
End Function


Guarda un po' il file e fammi sapere.

[Marius44]

Ciao a tutti
non sono riuscito a scaricare il file di Renato, quello di Alfredo si.

@alfrimpa
come al solito, ottima soluzione
Non ho capito, però, perchè ci sono 3 righe per la camera 200

@renatoelba
credo che 365/366 colonne siano difficoltose da spulciare. Secondo me sarebbe meglio usare 13 fogli (12 per i mesi ed uno di riepilogo) e nel foglio di riepilogo "richiamare" di volta in volta il mese interessato.

Ancora complimenti ad Alfredo e ciao a tutti,
Mario

[alfrimpa]

Ciao Mario grazie per i complimenti.

Fatti da uno come te sono un onore.

Un caro saluto.

[alfrimpa]

E poi una volta che viene l'idea risolutiva non è difficile tradurla in VBA

[renatoelba]

Grazie Alfredo, ma non mi funziona.
Come per il cerca.vert si ferma alla prima prenotazione sulla medesima camera.
E' proprio lo scoglio che volevo superare, a questo punto ci ero arrivato con cerca.vert nidificato in una funzione SE.
Hai voglia e pazienza di perderci ancora un po' di tempo?
Grazie mille
Renato

[renatoelba]

Se può servire il ragionamento che ho fatto io verso una possibile soluzione (che però non so tradurre in linguaggio di programmazione) è il seguente:
cercami la data (compresa tra le due della prima e della seconda colonna), in corrispondenza della camera x, se la trovi bene, mi restituisci "prenotata" se non la trovi (nel senso che trovi la camera x ma non la corrispondenza della data) cerca a partire dalla riga sotto, e così via...Grazie di nuovo
Renato

[alfrimpa]

Scusa Renato ma forse non mi sono applicato sufficientemente per capire qual era il tuo obiettivo.

Ora sono fuori comunque provo a fare un'ipotesi:

Se nella tabella di sinistra c'è due volte la camera 200 che ha come date la prima 1/1/15 e 5/1/ 15 e la seconda 7/1/15 e 10/1/15 tu vuoi che nella tabella a destra venga compilata la riga della camera 200 per i giorni compresi tra le due coppie di date?

Spero di non essere stato troppo contorto.

Cerca di rispondere presto perchè venerdì vado in ferie e non so se ce la faccio.

[renatoelba]

Voglio che siano rappresentati a destra tutti i giorni con le camere occupare: da una parte le prenotazioni, dall'altra un planning che le rappresenti TUTTE graficamente in modo immediato (io poi ci farei una formattazione condizionale per colorare le celle). Sul foglio che mi hai allegato a destra mi vengono riportate come prenotate solo le date della prima prenotazione, mentre in corrispondenza di ulteriori date prenotate a sinistra, a destra la camera sembrerebbe ancora libera.
Evidentemente non mi ero spiegato bene io, spero di aver chiarito.
Grazie mille
Renato

[renatoelba]

per chiarire: camera 200, 1/1/15 occupata, 2/1/15 occupata, 3/1/15 occupata, 4/1/15 occupata, 5/1/15 occupata, 6/1/15 vuota, 7/1/15 occupata, 8/1/15 occupata, 9/1/15 occupata, 10/1/15 occupata, 11/1/15 vuota, ecc....

[alfrimpa]

Ho capito quindi la mia ipotesi precedente era corretta?

Ossia nell'esempio che facevo prima la camera 200 deve avere "occupata" dall'1 al 5/1/15 e dal 7 al 10/1/15 giusto?

Domani mi ci metto e spero di terminare ma anche di riuscire prima di venerdì (in ferie non avrò il pc ma l'iPad si per cui posso comunque collegarmi al forum)

È una sfida che mi piace

[renatoelba]

Non avevo capito bene la tua precedente ipotesi. Da come avevi scritto ("i giorni compresi tra le due coppie di date") avevo capito che intendevi che a destra ci andassero i giorni nel mezzo (nel caso specifico il 6 gennaio) o tutti i giorni (dall'1 al 10). In realtà ci vanno i giorni dall'1 al 5 e dal 7 al 10, quindi il 6 vuota e dall'11 vuota. Poi se un'altro prenota sempre la 200, che ne so, dal 13 al 20, allora a destra si aggiungono le ultariori occupazioni delle camere. Ho visto uno che l'ha fatto su un tutorial su youtube, ma non ha spiegato come l'ha fatto, essendosi concentrato solo sulla formattazione condizionale
https://www.youtube.com/watch?v=-50Q-lDlMuQ
vedi un po'.
Grazie

[alfrimpa]

Allora avevo capito bene.

[alfrimpa]

Ho visto un po' il video; è fatto con il VBA.

La differenza con te è che nell'intestazione della tabella a destra tu hai le date e lui solo de numeri.

Ora vediamo un po'

[alfrimpa]

Ciao Renato

Ti allego il file renatoelba_3

http://www.filedropper.com/renatoelba3

Più di così non sono riuscito a fare ed al momento il lavoro è piuttosto "grezzo" comunque te ne spiego il funzionamento.

Quando nella tabella di sinistra vai ad inserire la data nel campo "a" automaticamente verrà compilata la tabella di destra colorando il fondo di giallo ed inserendo un "P" nelle celle.

Quello che non sono riuscito a fare è che ad una modifica della data non corrisponde un adeguamento della tabella a destra ma devi cancellare la zona selezionandola cliccando sul pulsante Cancella che ti ho messo in alto a sinistra.

Questo il codice della macro

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nRiga As Long
Dim da As Integer
Dim a As Integer
Dim rng As Range
Dim rng1 As Range
Dim cel As Range
If Not Intersect(Target, Range("c2:c1000")) Is Nothing Then
If Target.Rows.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
With Sheets("Foglio1").Range("e2:e1000")
            Set rng = .Find(What:=Target.Offset(0, -2).Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
               If Not rng Is Nothing Then
               nRiga = rng.Row
               da = Day(Target.Offset(0, -1).Value)
               a = Day(Target.Value)
               Set rng1 = Range(Cells(nRiga, da + 5), Cells(nRiga, a + 5))
               rng1.Interior.ColorIndex = xlNone
               rng1.ClearContents
               rng1.Interior.ColorIndex = 6
               For Each cel In rng1
                cel.Value = "P"
               Next cel
               End If
End With
End If
End Sub

Prova un po' il file e fammi sapere (anche se ora vado in ferie per cui non ho pc)

Ho anche provato a guardare il video che mi hai segnalato (che fa anche la parte che manca a me modifiche cancellazioni) ma non sono riuscito ad adattarlo, per il momento al tuo caso).

[renatoelba]

Grazie ma non va bene.
Io devo creare il planning da un elenco prenotazioni esistente.
Da quanto ho capito il foglio che hai predisposto lavora invece sulla tabella di sinistra e, al momento della sua compilazione, va a scrivere a destra. Invece deve funzionare così:
per ogni cella xy della tabella di destra (in cui x va da F a g, dove g corrispode a F aumentato del numero di giorni di apertura della struttura, e y va da 2 a c, dove c è il numero di camere), individuata dall'incrocio tra una riga (che corrisponde alla camera, descritta in colonna E) ed una colonna (che corrisponde alla data, descritta alla riga 1), deve effettuarsi una ricerca sulla tabella di sinistra, per ogni sua riga i (con i che va da 2, considerando un'intestazione, a n, dove n è il numero delle prenotazioni), verificando tre condizioni:
1) che il valore della prima colonna (E) della tabella di destra corrisponda al valore della prima colonna (A) della tabella di sinistra;
2) che il valore della data corrispondente alla cella (x1) della tabella di destra sia maggiore o uguale al valore della seconda colonna della riga i (bi);
3) che il valore predetto (x1) sia minore del valore della terza colonna della riga i (Ci, dove ì è la variabile su cui compiere il ciclo, al fine di far fare il controllo su tutte le righe, vale a dire su tutte le prenotazioni).
Se VERO allora P, se falso VUOTO.
La logica è questa, non so tradurla in visual basic, ma mi sa che dovrò mettermi a studiarlo durante le ferie...
Grazie
Renato