Cerca.vert e risultati multipli
Andrea
mi restituisca tutte le occorrenze trovate, googlando ho trovato una
funzione personalizzata (la traduzione è mia, scusate il
provincialismo:-))
Public Function CercaTutto$(Stringa As String, Area As Range, Colonna
As Long)
'CercaTutto cerca "Stringa" nella prima colonna di "Area" e ritorna il
corrispondente
'valore presente in "Colonna" se "Stringa" viene trovata. Tutti i
corrispondenti valori vengono
'memorizzati e restituiti come stringa concatenata.
Dim i As Long
If Colonna > Area.Columns.Count Then
CercaTutto = CVErr(xlErrValue)
Exit Function
End If
CercaTutto = ""
For i = 1 To Area.Rows.Count
If Area(i, 1).Text = Stringa Then
CercaTutto = CercaTutto & Area(i, Colonna).Text & "; "
End If
Next i
End Function
Solo che è praticamente inutilizzabile a causa delle lentezza
esasperante.
Qualcuno ha una soluzione migliore?
Grazie, Andrea
Tiziano Marmiroli
>Ciao a tutti, avrei bisogno di una formula come il cerca.vert, ma che
>mi restituisca tutte le occorrenze trovate, googlando ho trovato una
>funzione personalizzata (la traduzione � mia, scusate il
>provincialismo:-))
>
>Public Function CercaTutto$(Stringa As String, Area As Range, Colonna
>As Long)
>Qualcuno ha una soluzione migliore?
Subject: trovare record con dati non univoci
Date: Fri, 18 Mar 2005 02:49:01 -0800
http://www.google.it/groups?threadm=914394E0-7946-4DB6...@microsoft.com
Url breve: http://peek.snurl.com/findnth
Facci sapere se e come hai risolto, grazie.
--
Tiziano Marmiroli
Microsoft MVP - Office System
http://www.riolab.org
Andrea
cui mi hai rimandato faccia al caso mio, io ho due tabelle, nella
prima un elenco di codici, nella seconda un elenco di codici, anche
ripetuti, con a fianco una ubicazione (alfanumerica), vorrei riportate
in una colonna della prima tabella le diverse ubicazioni, separate da
virgola, della seconda tabella.
Il cerca.vert ovviamente mi da solo la prima che trova.
Grazie, Andrea
On 26 Feb, 16:17, Tiziano Marmiroli <t.marmir...@mvps.org> wrote:
> Il Fri, 26 Feb 2010 07:09:56 -0800 (PST), Andrea ha scritto:
>
[snip]
>
> Subject: trovare record con dati non univoci
> Date: Fri, 18 Mar 2005 02:49:01 -0800
> http://www.google.it/groups?threadm=914394E0-7946-4DB6-9B6E-49157572B...@microsoft.com
Scossa
6e466e...@t23g2000yqt.googlegroups.com>, geniu...@gmail.com says...
> Solo che è praticamente inutilizzabile a causa delle lentezza
> esasperante.
>
Prova ad usare il metodo .Find.
Quella che segue mi sembra abbastanza veloce.
Questo un esempio:
I tuoi dai in A3:F9000
la colonna di ricerca B3:B9000
la colonna da cui restituire la cella è la D (quindi 2 colonne a dx di
quella di ricerca
=CercaMVert(F3; $A$3:$A$9000;2)
oppure
la colonna da cui restituire la cella è la A (quindi 1 colonna a sx di
quella di ricerca
=CercaMVert(F3; $A$3:$A$9000;-1)
' in un modulo standard
Public Function CercaMVert( _
ByVal sWath As String, _
ByRef rng As Range, _
ByVal lCol As Long) As Variant
'
'----------------------------------------------------
' Ricerca *sWath* nel range *Rng* (di 1 sola colonna)
' e restituisce *concatenandoli* i valori delle celle
' spostate *lCol* rispetto alla cella dove il valore
' è stato trovato.
'----------------------------------------------------
Dim rFound As Range
Dim nRow As Long
Dim nLastRow As Long
Dim rLastCell As Range
Dim bCalc As XlCalculation
With Application
bCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
nRow = Application.WorksheetFunction.Max(rng.Row - 1, 1)
Set rng = Union(Cells(nRow, rng.Column), rng)
Set rLastCell = Cells(rng.Row + rng.Rows.Count - 1, 1)
nLastRow = rLastCell.Row
Set rFound = rng.Find(sWath, LookIn:=xlValues)
If Not rFound Is Nothing Then
Do
CercaMVert = CercaMVert & _
rFound.Offset(0, lCol).Value & ";"
nRow = rFound.Offset(0, lCol).Row
Set rng = Range(rFound.Offset(1).Address & ":" _
& rLastCell.Address)
Set rFound = rng.Find(sWath, LookIn:=xlValues)
Loop While Not rFound Is Nothing And nRow < nLastRow
Else
CercaMVert = CVErr(Excel.xlErrNA)
End If
With Application
.Calculation = bCalc
.ScreenUpdating = True
End With
End Function
'-------------------------------
Questa invece purtroppo FALLISCE nel metodo .findnext e non capisco
perche'!!
'--------------------------------
Public Function CercaMVert2( _
ByVal sWath As String, _
ByRef rng As Range, _
ByVal lCol As Long) As Variant
'
'----------------------------------------------------
' Ricerca *sWath* nel range *Rng* (di 1 sola colonna)
' e restituisce *concatenandoli* i valori delle celle
' spostate *lCol* rispetto alla cella dove il valore
' è stato trovato.
'----------------------------------------------------
Dim rFound As Excel.Range
Dim bCalc As XlCalculation
Dim nRow As Long
With Application
bCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set rFound = rng.Find(sWath, LookIn:=xlValues)
If Not rFound Is Nothing Then
Do
CercaMVert2 = CercaMVert2 & _
rFound.Offset(0, lCol).Value & ";"
Set rFound = rng.FindNext()
Loop While Not rFound Is Nothing
Else
CercaMVert2 = CVErr(Excel.xlErrNA)
End If
With Application
.Calculation = bCalc
.ScreenUpdating = True
End With
End Function
'------------------------------------------
Sarebbe interessante se qualcuno volesse provarla e riuscisse a spiegare
perche'
set rFound = .findnext(rFound)
da' subito nothing
Comunque fai sapere se hai risolto, grazie.
Bye!
Scossa
--
Bye!
Scossa
Andrea
restituisce #VALORE, tieni presente che devo confrontare due fogli
distinti, con un campo in comune.
Grazie, Andrea
On 26 Feb, 22:38, Scossa <Scossa...@gmail.com> wrote:
> In article <ed52e244-7df2-4d8b-a66d-
> 6e466eae1...@t23g2000yqt.googlegroups.com>, genius2...@gmail.com says...
>
> > Solo che è praticamente inutilizzabile a causa delle lentezza
> > esasperante.
>
> Prova ad usare il metodo .Find.
[snip]
> --
> Bye!
> Scossa
Scossa
@k41g2000yqm.googlegroups.com>, geniu...@gmail.com says...
> Grazie anche a te del suggerimento, ho provato la funzione ma mi
> restituisce #VALORE, tieni presente che devo confrontare due fogli
> distinti, con un campo in comune.
Ok, scusa non avevo tenuto conto dei due fogli diversi.
Prova questa variante:
'------------------------------------------
Public Function CercaMVert( _
ByVal sWath As String, _
ByRef rng As Range, _
ByVal lCol As Long) As Variant
'
'----------------------------------------------------
' di Scossa
' Ricerca *sWath* nel range *Rng* (di 1 sola colonna)
' e restituisce *concatenandoli* i valori delle celle
' spostate *lCol* rispetto alla cella dove il valore
' è stato trovato.
'----------------------------------------------------
Dim rFound As Range
Dim nRow As Long
Dim nLastRow As Long
Dim rLastCell As Range
Dim bCalc As XlCalculation
Dim shFrom As Worksheet
With Application
bCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set shFrom = rng.Parent
nRow = Application.WorksheetFunction.Max(rng.Row - 1, 1)
Set rng = Union(shFrom.Cells(nRow, rng.Column), rng)
Set rLastCell = shFrom.Cells(rng.Row + rng.Rows.Count - 1, rng.Column)
nLastRow = rLastCell.Row
Set rFound = rng.Find(sWath, LookIn:=xlValues)
If Not rFound Is Nothing Then
Do
CercaMVert = CercaMVert & _
rFound.Offset(0, lCol).Value & ";"
nRow = rFound.Offset(0, lCol).Row
Set rng = shFrom.Range(rFound.Offset(1).Address & ":" _
& rLastCell.Address)
Set rFound = rng.Find(sWath, LookIn:=xlValues)
Loop While Not rFound Is Nothing And nRow < nLastRow
Else
CercaMVert = CVErr(Excel.xlErrNA)
End If
Set shFrom = Nothing
With Application
.Calculation = bCalc
.ScreenUpdating = True
End With
End Function
'----------------------------------
Fai sapere se hai risolto, grazie,
--
Bye!
Scossa
Andrea
colonne e funziona "quasi", non restituisce infatti il secondo valore,
nel mio foglio invece restituisce ";" anche multiipli, ma nessun
valore, restituisce invece correttamente #N/D per i valori non
trovati, preferirei restituisse "", mi eviterebbe di metterci un SE.
Foglio 1
Codice Ubicazione
123 a
123 b
123 c
234 d
234 e
345 f
345 g
Foglio due con la formula =CercaMVert(A1;Foglio1!A2:A8;1)
Codice Ubicazione
123 a;c;
234 d;e;
345 f;g;
Grazie ancora,
Andrea
On 28 Feb, 11:40, Scossa <Scossa...@gmail.com> wrote:
> In article <fea25d25-1bac-4ddc-a775-7b56471e3320
> @k41g2000yqm.googlegroups.com>, genius2...@gmail.com says...
>
> > Grazie anche a te del suggerimento, ho provato la funzione ma mi
> > restituisce #VALORE, tieni presente che devo confrontare due fogli
> > distinti, con un campo in comune.
>
> Ok, scusa non avevo tenuto conto dei due fogli diversi.
> Prova questa variante:
[snip]
Andrea
colonne e funziona "quasi", non restituisce infatti il secondo valore,
nel mio foglio invece restituisce ";" anche multiipli, ma nessun
valore, restituisce invece correttamente #N/D per i valori non
trovati, preferirei restituisse "", mi eviterebbe di metterci un SE.
Foglio 1
Codice Ubicazione
123 a
123 b
123 c
234 d
234 e
345 f
345 g
Foglio due con la formula =CercaMVert(A1;Foglio1!A2:A8;1)
Codice Ubicazione
123 a;c;
234 d;e;
345 f;g;
Grazie ancora,
Andrea
On 28 Feb, 11:40, Scossa <Scossa...@gmail.com> wrote:
> In article <fea25d25-1bac-4ddc-a775-7b56471e3320
> @k41g2000yqm.googlegroups.com>, genius2...@gmail.com says...
>
> > Grazie anche a te del suggerimento, ho provato la funzione ma mi
> > restituisce #VALORE, tieni presente che devo confrontare due fogli
> > distinti, con un campo in comune.
>
> Ok, scusa non avevo tenuto conto dei due fogli diversi.
> Prova questa variante:
[snip]
Andrea
"quasi" anche nei miei fogli, ma ogni tanto non trova qualcosa.
Grazie, Andrea
Andrea
"quasi" anche nei miei fogli, ma ogni tanto non trova qualcosa.
Grazie, Andrea
On 28 Feb, 12:15, Andrea <genius2...@gmail.com> wrote:
Andrea
"quasi" anche nei miei fogli, ma ogni tanto non trova qualcosa.
Grazie, Andrea
On 28 Feb, 12:15, Andrea <genius2...@gmail.com> wrote:
Andrea
Cambiando il range da Foglio1!A2:A5000 a Foglio1!A:A, nel mio foglio
sembra trovare tutto, mentre nel foglio di prova che ho postato prima,
manca sempre un risultato.
Grazie, Andrea
Andrea
Cambiando il range da Foglio1!A2:A5000 a Foglio1!A:A, nel mio foglio
sembra trovare tutto, mentre nel foglio di prova che ho postato prima,
manca sempre un risultato.
Grazie, Andrea
Scossa
> >
> > > Foglio due con la formula =CercaMVert(A1;Foglio1!A2:A8;1)
> > > Codice Ubicazione
> > > 123 a;c;
> > > 234 d;e;
> > > 345 f;g;
Intanto ti consiglio di usare i riferimetni assoluti:
=CercaMVert(A1;Foglio1!$A$2:$A$8;1)
così se copi la formula l'intervallo di ricerca non si sposta.
Sul fatto che non trova tutti i dati hai ragione, il problema è che il
metodo .find inizia la ricerca "dopo" la prima cella del range che resta
quindi esclusa, da lì l'istruzione:
Set rng = Union(shFrom.Cells(nRow, rng.Column), rng)
che incollava al rng di ricerca la cella immediatamente sopra.
Ma questo andrebbe fatto anche nel ciclo do...loop
Ci penso su, ma mi sembra che si incasini un po' troppo.
Tutto perchè il metodo .findnext non si comporta come dovrebbe.
Se qualche GURU volesse illuminarci .....
--
Bye!
Scossa
Andrea
Foglio 1
Codice Ubicazione
ciao a
ciao b
ciao c
ciao d
ciao e
ciao f
ciao g
ciao h
ciao i
Foglio 2 con formula =CercaMVert(A2;Foglio1!$A$2:$A$10;1)
Codice Ubicazione
ciao a;c;e;g;i;
Ne trova 1 si e 1 no.
Grazie, Andrea
P.S. mi scuso per i post doppi, ma credo sia colpa di google gruppi
Scossa
@g7g2000yqe.googlegroups.com>, geniu...@gmail.com says...
> Foglio 2 con formula =CercaMVert(A2;Foglio1!$A$2:$A$10;1)
> Codice Ubicazione
> ciao a;c;e;g;i;
>
> Ne trova 1 si e 1 no.
>
Questa, ho fatto un po' di prove, mi sembra funzioni come dovrebbe:
'----------------------------------------------------
Public Function CercaMVert( _
ByVal sWath As String, _
ByRef rng As Range, _
ByVal lCol As Long) As Variant
'
'----------------------------------------------------
' di Scossa
' Ricerca *sWath* nel range *Rng* (di 1 sola colonna)
' e restituisce *concatenandoli* i valori delle celle
' spostate *lCol* rispetto alla cella dove il valore
' è stato trovato.
'----------------------------------------------------
Dim rFound As Range
Dim nRow As Long
Dim nLastRow As Long
Dim rLastCell As Range
Dim bCalc As XlCalculation
Dim shFrom As Worksheet
Dim cAddress As String
With Application
bCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set shFrom = rng.Parent
nRow = Application.WorksheetFunction.Max(rng.Row - 1, 1)
Set rRngBck = rng
Set rng = Union(shFrom.Cells(nRow, rng.Column), rng)
Set rLastCell = shFrom.Cells(rng.Row + rng.Rows.Count - 1, rng.Column)
nLastRow = rLastCell.Row
Set rFound = rng.Find(sWath, LookIn:=xlValues)
If Not rFound Is Nothing Then
cAddress = rFound.Address
Do
CercaMVert = CercaMVert & _
rFound.Offset(0, lCol).Value & ";"
Set rng = Intersect(rng, rng.Offset(rFound.Row - rng.Row))
nRow = rng.Row
cAddress = rFound.Address
Set rFound = rng.Find(sWath, LookIn:=xlValues)
Loop While Not rFound Is Nothing And nRow < nLastRow And rFound.Address
<> cAddress
CercaMVert = Left(CercaMVert, Len(CercaMVert) - 1)
Else
CercaMVert = CVErr(Excel.xlErrNA)
End If
Set shFrom = Nothing
With Application
.Calculation = bCalc
.ScreenUpdating = True
End With
End Function
'--------------------------------------------
Fai sapere, grazie.
--
Bye!
Scossa
Andrea
Grazie 1000
Ciao, Andrea
On 28 Feb, 16:27, Scossa <Scossa...@gmail.com> wrote:
> In article <6db98203-85ac-47f7-a883-3ed3a5bb45a7
> @g7g2000yqe.googlegroups.com>, genius2...@gmail.com says...
>
> > Foglio 2 con formula =CercaMVert(A2;Foglio1!$A$2:$A$10;1)
> > Codice Ubicazione
> > ciao a;c;e;g;i;
>
> > Ne trova 1 si e 1 no.
>
> Questa, ho fatto un po' di prove, mi sembra funzioni come dovrebbe:
[snip]
Scossa
016747...@g28g2000yqh.googlegroups.com>, geniu...@gmail.com says...
> Adesso è perfetta e velocissima!
>
> Grazie 1000
Grazie a te per il cortese riscontro.
--
Bye!
Scossa
Andrea
funzione non funziona piů, si comporta come il cerca.vert classico e
restituisce solo il primo valore che trova. Si comporta in questo modo
anche in files in cui ha sempre funzionato!
Qualche idea per il debug?
Grazie, Andrea
Il 28/02/2010 16.43, Andrea ha scritto:
> Adesso č perfetta e velocissima!
Scossa
> Scusate se "riesumo" questo vecchio thread, ma da qualche giorno questa
> restituisce solo il primo valore che trova. Si comporta in questo modo
> anche in files in cui ha sempre funzionato!
> Qualche idea per il debug?
Molto strano.
Sicuro di non aver cambiato qualcosa (scritto =cerca.vert anzichè
=cercamvert)?
puoi depositare uno dei file su un server di sharing (p.e:
rapidshare.com)?
Bye!
Scossa
Andrea M.
http://rapidshare.com/files/366722452/TestScossa.xls.html
Grazie per l'aiuto.
Ciao, Andrea
Scossa
> Ciao, ho creato un piccolo file di test, eccoti il link
>
> http://rapidshare.com/files/366722452/TestScossa.xls.html
>
> Grazie per l'aiuto.
Stai sbagliando la chiamata:
=PERSONAL.XLS!CercaMVert(E2;A2:B5;1)
leggi bene gli argomenti della funzione:
Public Function CercaMVert( _
ByVal sWath As String, _
ByRef rng As Range, _
ByVal lCol As Long) As Variant
'
'----------------------------------------------------
' di Scossa
' Ricerca *sWath* nel range *Rng* (di 1 sola colonna)
' e restituisce *concatenandoli* i valori delle celle
' spostate *lCol* rispetto alla cella dove il valore
' è stato trovato.
Rng deve eseere di una sola colonna, quindi A2:A5
=PERSONAL.XLS!CercaMVert(E2;A2:A5;1)
Fai sapere se hai risolto, grazie.
--
Bye!
Scossa
Andrea M.
*correttamente*.
Grazie ancora.
Ciao, Andrea
Scossa
> Perdindirindina :-) certo che ho risolto, è bastato applicarla
> *correttamente*.
>
> Grazie ancora.
Grazie a te per il riscontro.
--
Bye!
Scossa
frances...@gmail.com
A B
1 01/01/2014 evento1
2 01/01/2014 evento2
3 11/04/2014 evento3
4 25/04/2014 evento4
5 01/01/2014 evento5
e devo estrapolare (in modo indipendente) le varie occorrenze. Il risultato dovrebbe essere questo
data occorrenza1
occorrenza2
occorrenzaN
e, nella fattispecie:
01/01/2014 evento1
evento2
evento5
Ho provato a usare la udf CercaMVert(), ma mi restituisce un errore nella riga che contiene "<> cAddress".
Mi puoi aiutare?
cassanel...@gmail.com
elon...@gmail.com
facendo una ricerca sul web, sono giunta a questo gruppo di conversazione.
ho utilizzato la funzione CercaMultiVert e funziona come dovuto, il mio unico problema e che io non vorrei avere i risultati concatenati, ma vorrei che si trovassero su colonne diverse oppure concatenati da qualche simbolo speciale Tipo */&°#.
Sperando che qualcuno mi possa aiutare.
Tanti Saluti.