Cerca Verticale fa cilecca !

[gianca53Partecipante gianca53]

In un mio  progettino utilizzo questa formula che sulla base di un elenco identico  esistente su due fogli diversi , cerca e trova il valore corrispondente e lo moltiplica x b7 quindi divide per D7

=SE.ERRORE($B7*CERCA.VERT($C7;Ricerca!$B$2:$AP$40;CONFRONTA(F$4;Ricerca!$C$1:$AQ$1;0)+1;0)/$D7;"")

La formula funziona bene, però io ho una macro che nel foglio “Ricerca”  elimina righe che non servono e le righe vuote , quindi viene alterato il range di ricerca B2: AP 40. Quest’ultimo, per effetto delle cancellazioni fatte,  diventa, per es. AP 20 , AP 15 etc.  ovvero si riduce delle righe cancellate.

Qualche alternativa ? 

[Marius44Moderatore Marius44 Karma: 53 pts]

Ciao

Una precisazione: se tu fai la ricerca in quell'intervallo ( Ricerca!$B$2:$AP$40 ) vuol dire che ti interessa i dati di quell'intervallo (anche se ci sono celle o intere righe e/o colonne vuote). Taglia la testa al toro! Non consentire l'eliminazione di righe o colonne che intersecano l'intervallo.

Ciao,

Mario

[gianca53Partecipante gianca53]

Ciao  Marius, il problema è che  su quel foglio, sempre da macro, previa ricerca mirata,  si presentano 10 o 20 righe  di un elenco di 800 righe, tra queste faccio la cernita e magari ne resta una sola utile. Sempre su  questo foglio posso ripetere la ricerca e quindi ottenere  altre n righe etc.  vedi bene che se lo ripulisco ottengo una migliore leggibilità, lo riduco da n righe solo a quelle che mi interessa mantenere,  però cosi facendo altero la formula !  

Forse, nella macro di  cancella righe(vuote)  anzichè  delete rows  potrei provare con nascondi righe vuote ? Adesso provo .

 

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Quindi elimini delle righe dal database? se poi devi "ripetere la ricerca" devi reimportare daccapo la base dati? e poi se hai già una macro che fa qualcosa significa che accetti di abilitare l'esecuzione di codice e quindi che bisogno hai di formule? Fai tutto da codice, senza eliminare righe, filtrando solo quelle che ti interessano e se proprio devi presentare il risultato fai una copia su u altro foglio, ma non tocchi il db originale.

Ad ogni modo la macro potrebbe adattare la formula sulla base del nuovo range (ricavando con CurrentRegion le nuove coordinate) e quindi scriverla ex abrupto nel foglio al posto della precedente. Rimane la considerazione che ho fatto un attimo fa 🙂

Queste domande nascono perchè non è stato descritto (o non l'ho capito) lo scenario.

[gianca53Partecipante gianca53]

Lo scenario è un po' complicato, provo a descriverlo :

1 ) Ho un foglio che funge da database aggiornabile = 41 colonne x 800 righe ,

2) ho un secondo foglio nel quale faccio la ricerca (x nome) dei dati presenti su fo.1 -  poi seleziono quello che interessa, cancellando o nascondendo le righe inutili o vuote ( tutto da macro)

3) infine trasferisco la sintesi cosi ottenuta su foglio3  sul quale imposto altri valori e faccio il ricalcolo .

 

Certo è che se riuscissi a fare il ricalcolo via VBA sarebbe la cosa migliore , e forse la più "pulita" , però non ho la minima idea di come impostare il lavoro .

 

Ora giusto per mia curiosità ho risolto il problema della formula con questa macro (trovata qui ) che devo applicare su entrambi i fogli . in pratica nascondo le righe e non ho alterazione della formula stessa . certo che chi vede il file si chiederà  il perchè le righe siano  numerate : 1,2,3,5,7,12,19, 34 , 56 etc 

`Sub NascondiRigheR()
  Dim ws As Worksheet
  Dim i As Long
  Set ws = Sheets("Ricerca")
ws.Activate
  Application.ScreenUpdating = False
  With ws
    i = .Range("b" & .Rows.Count).End(xlUp).Row
    .Cells.EntireRow.Hidden = False
    For i = i To 2 Step -1
      If WorksheetFunction.CountBlank(.Range(Cells(i, 1), Cells(i, 42))) Then
        .Rows(i).EntireRow.Hidden = True
      End If
    Next i
  End With
  Application.ScreenUpdating = True
  Set ws = Nothing
End Sub
`

[gianca53Partecipante gianca53]

Ho risolto  il problema  della formula con questo codice :

Private Sub NascondiRigheR()
  Dim ws As Worksheet
  Dim i As Long
  Set ws = Sheets("Ricerca")
ws.Activate
  Application.ScreenUpdating = False
  With ws
    i = .Range("b" & .Rows.Count).End(xlUp).Row
    .Cells.EntireRow.Hidden = False
    For i = i To 2 Step -1
      If WorksheetFunction.CountBlank(.Range(Cells(i, 1), Cells(i, 42))) Then
        .Rows(i).EntireRow.Hidden = True
      End If
    Next i
  End With
  Application.ScreenUpdating = True
  Set ws = Nothing
End Sub

[gianca53Partecipante gianca53]

però non sono per niente soddisfatto! 

"Queste domande nascono perchè non è stato descritto (o non l'ho capito) lo scenario." 

é vero , provo a spiegare: 

1) Foglio1 funge da database , consta di ca 45 colonne per 800 righe ,

2) fo.2  con macro faccio la ricerca per nome generico e ottengo n righe complete da fo.1 . Ne faccio la selezione contrassegnando con X le righe che mi interessano e cancello le altre. Indi copio la sintesi sul successivo fo.3

3) fo3. Qui  per ogni rigo - inserisco un valore (b5 e seg. ) per il quale motiplicare tutti i dati importati . Una sorta di riparametrazione dei valori . Ed è su quest'ultimo fo che applico la formula di cui al post.1 la quale è il meglio che ho potuto fare stante la mia scarsa conoscenza del VBA .

 

 

[Luca73 Luca73]

Forse potresti definire un intervallo con scarto potrebbe risolvere

 

[gianca53Partecipante gianca53]

Si.  ma vorrei evitare le formule, se te ne cancellano una  sono pasticci .  E poi con la macro sopra avrei già risolto , restano le mie perplessità di fondo su tale sistema . E' vero che potrei proteggere il foglio però ...  i dubbi restano.   

[gianca53Partecipante gianca53]

Giusto per imparare ho  "quasi " adattato la macro di Vecchiofrac  , fornita ad altro utente, in modo da renderla  simile a ciò  che succede nel mio foglio.  Quasi,  perchè la macro funziona ni , nel senso che non è puntuale nei trasferimenti , ovvero  non ho capito come adattarla   .

Poi, la parte più difficile,   la parte di calcolo utilizzando la colonna " Mio input"  già prevista ma che so  come impostarla. 

Allego file se qualcuno volesse dare un occhiata , tutti i consigli sono ben accetti .

Allegati:

You must be logged in to view attached files.

[vecchio fracSenior Moderator vecchio frac Karma: 245 pts]

Allora evita del tutto la formula, traducila in VBA, calcola il risultato e inseriscilo dove deve andare. CERCA.VERT è in sostanza un Find applicato a un Range.

[gianca53Partecipante gianca53]

Non ho risolto, ma fa lo stesso . Ho ripiegato su altro .

Comunque anche il tentativo con Hidden al posto di  Delete  risolve il problema solo parzialmente nel senso che agli effetti di un successivo trasferimento - non altera la formula - ma  copia anche le righe nascoste.  e qusto non va bene perchè altera  la  struttura del foglio ricevente. 

Ora, intanto che scrivo mi è  venuto in mente un delete blank  nella procedura di paste special, verifico. 

[Autore]

Articoli