VBA Contare elementi Range da una Find

Discussione:

(troppo vecchio per rispondere)

Riddler ?

2006-09-20 21:56:02 UTC

Salve a tutti,

credevo fosse semplice, ma sto prendendo una cantonata ...

Devo cercare un determinato valore e ciclare su di esso per fare delle
operazioni, ma l'istruzione

Dim rng_in as Range
Set rng_in = SH.Range("H:H").Find("xxx", LookIn:=xlValues)

So che ci son 3 valori ( sto facendo i test di prova ... )

se faccio
tot = rng_in.Cells.Count
o
tot = rng_in.Rows.Count

Ottengo il valore 1

Io vorrei sapere da quante righe è formato il range e ciclando su
quelle righe andare a recuperare i valori di altre celle su quelle
determinate righe.

Grazie a tutti anticipatamente

Riddler ?

Norman Jones

2006-09-20 22:21:05 UTC

Permalink

Ciao Riddler,

'----------------
credevo fosse semplice, ma sto prendendo una cantonata ...

Devo cercare un determinato valore e ciclare su di esso per fare delle
operazioni, ma l'istruzione

Dim rng_in as Range
Set rng_in = SH.Range("H:H").Find("xxx", LookIn:=xlValues)

So che ci son 3 valori ( sto facendo i test di prova ... )

se faccio
tot = rng_in.Cells.Count
o
tot = rng_in.Rows.Count

Ottengo il valore 1

Io vorrei sapere da quante righe è formato il range e ciclando su
quelle righe andare a recuperare i valori di altre celle su quelle
determinate righe.

'----------------

Forse prova:

Application.CountIf(Range("H:H"), xxx)

---
Regards,
Norman

elby

2006-09-20 22:24:20 UTC

Permalink

Post by Riddler ?
Salve a tutti,
credevo fosse semplice, ma sto prendendo una cantonata ...
Devo cercare un determinato valore e ciclare su di esso per fare delle
operazioni, ma l'istruzione
Dim rng_in as Range
Set rng_in = SH.Range("H:H").Find("xxx", LookIn:=xlValues)
So che ci son 3 valori ( sto facendo i test di prova ... )
se faccio
tot = rng_in.Cells.Count
o
tot = rng_in.Rows.Count
Ottengo il valore 1
Io vorrei sapere da quante righe è formato il range e ciclando su
quelle righe andare a recuperare i valori di altre celle su quelle
determinate righe.
Grazie a tutti anticipatamente
Riddler ?

Ciao Riddler
Il metodo Find dell'oggetto Range ti restituisce la cella con la prima
occorrenza, sempre che il valore cercato con i criteri di ricerca
esista . Quindi il numero di celle del range restituito da un metodo
Find non può essere che uno. Ti occorre per completare l'opera il
metodo FindNext.
Ciao Elio

Riddler ?

2006-09-20 22:30:54 UTC

Permalink

Grazie Norman e grazie Elby

Il Find e Find Next li utilizzavo per scorrere tutte le occorrenze, ma
avevo necessità di avere da subito il totale delle occorrenze ...

Application.CountIf(Range("H:H"), "xxx")

NON era al corrente di questa istruzione ...

Ancora una volta mi avete tolto dai pasticci .-)

Grazie a tutti

Riddler ?

Norman Jones

2006-09-20 22:38:36 UTC

Permalink

Ciao Riddler,

Post by Riddler ?
Application.CountIf(Range("H:H"), "xxx")
NON era al corrente di questa istruzione ...

Vedi nell'Help di VBA "Proprietà WorksheetFunction"

---
Regards,
Norman

Riddler ?

2006-09-21 07:48:05 UTC

Permalink

Post by Norman Jones
Ciao Riddler,

Post by Riddler ?
Application.CountIf(Range("H:H"), "xxx")
NON era al corrente di questa istruzione ...

Vedi nell'Help di VBA "Proprietà WorksheetFunction"

.-)

GraSSSie!

C'è SEMPRE da imparare

P.S. ho "rovistato" un pò nell' help, ma non ho trovato una cosina ...
ora mi rendo conto di essere meno "espero" con le formule in Excel che
con VBA ( grazie al vostro aiuto sono migliorato molto in queste
settimane :-))) ... ) quello che stato cercando è questo:

Con la Find e Find Next scorro se ci sono tutte le occorrenze di "xxx"
e faccio le mie operazioni ... è possibile oltre alla dritta
suggeritami di sapere il numero di occorrenze anche di sapere in che
posizione si trova l'ultima occorrenza.

Esempio

1 xxx
2 xxx
3 ooo
4 ppp
5 uuu
6 eee
7 www
8 xxx
9 aaa

In questo caso avrei 3 occorrenze scandite con il Find + Find Next come
posso sapere auql'è l'ultima occorrenza? ( quindi sapare che si trova
in posizione 8 )
Anche questo come il numero totale lo devo sapere prima, perchè SOLO
sull'ultima devo fare una operazione in più ...

Grazie ancora a tutti

Riddler ?

Norman Jones

2006-09-21 08:17:06 UTC

Permalink

Ciao Riddler,

'----------------------
P.S. ho "rovistato" un pò nell' help, ma non ho trovato una cosina ...
ora mi rendo conto di essere meno "espero" con le formule in Excel che
con VBA ( grazie al vostro aiuto sono migliorato molto in queste
settimane :-))) ... ) quello che stato cercando è questo:

Con la Find e Find Next scorro se ci sono tutte le occorrenze di "xxx"
e faccio le mie operazioni ... è possibile oltre alla dritta
suggeritami di sapere il numero di occorrenze anche di sapere in che
posizione si trova l'ultima occorrenza.

Esempio

1 xxx
2 xxx
3 ooo
4 ppp
5 uuu
6 eee
7 www
8 xxx
9 aaa

In questo caso avrei 3 occorrenze scandite con il Find + Find Next come
posso sapere auql'è l'ultima occorrenza? ( quindi sapare che si trova
in posizione 8 )
Anche questo come il numero totale lo devo sapere prima, perchè SOLO
sull'ultima devo fare una operazione in più ...

'----------------------

Cosa intendi con "l'ultima occorrenza."?

L'ultima occorenza sarebbe l'ultima colonna oppure l'ultima riga? i.e., con
xxx in A500 e anche in AA499, quale sarebbe l'ultima.

Risolto questo problema, *non* e' necessario sapere il numero
dell'occorenze. Invece, potresti utilizare una delle due funzioni:

'=============>>
Function LastRowAddress(SH As Worksheet, Cosa As Variant)
On Error Resume Next
LastRow = SH.Cells.Find(What:=Cosa, _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Address
On Error GoTo 0
End Function

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

Function LastColAddress(SH As Worksheet, Cosa As Variant)
On Error Resume Next
LastCol = SH.Cells.Find(What:=Cosa, _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Address
On Error GoTo 0
End Function
'<<=============

Ad esempio, con diversi occorenze di "pippo", prova:

'=============>>
Public Sub TestMe()
MsgBox LastColAddress(ActiveSheet, "pippo")
MsgBox LastRowAddress(ActiveSheet, "pippo")
End Sub
'<<=============

---
Regards,
Norman

Norman Jones

2006-09-21 08:34:58 UTC

Permalink

Ciao Riddler,

le due funzione erano intese come:

'=============>>
Function LastRowAddress(SH As Worksheet, Cosa As Variant)
On Error Resume Next
LastRowAddress = SH.Cells.Find(What:=Cosa, _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Address
On Error GoTo 0
End Function

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

Function LastColAddress(SH As Worksheet, Cosa As Variant)
On Error Resume Next
LastColAddress = SH.Cells.Find(What:=Cosa, _
After:=SH.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Address
On Error GoTo 0
End Function
'<<=============

A proposito, se i dati da cercare fossero nella stessa colonna o la stessa
riga, chiaramente le du funzioni restituirebbero lo stesso risultato.

---
Regards,
Norman

Riddler ?

2006-09-21 08:51:37 UTC

Permalink

Post by Norman Jones
L'ultima occorenza sarebbe l'ultima colonna oppure l'ultima riga? i.e., con
xxx in A500 e anche in AA499, quale sarebbe l'ultima.

Grazie Norman come SEMPRE sei stato illuminante ed esaustivo .-)

L'ultima occorrenza è sulla riga.

Post by Norman Jones
Risolto questo problema, *non* e' necessario sapere il numero
dell'occorenze.

Purtroppo questo mi serve per determinare un altra cosa dopo aver
scandito tutte le occorrenze "xxx"
Grazie queste sono "preziose" in ogni caso ... archivio e metto da
parte ... :-)

L mia esigenza è

Conto quante occorrenze ho di "xxx"
Se TUTTE le righe che hanno "xxx" hanno anche in un altra colonna una
cella NON vuota significa che quel documento è completo e durante la
fase di creazione dei documenti la dicitura completo va riportata SOLO
sull'ultimo quello che ha la data più recente ...

Mi sono accorto adesso che le cose si complicano ... se le date non
sono in ordine, cioè la più recente è in basso ... come posso
stabilire quelè la più recente?

Altrimenti è incompleto.

C ... H I J
1 01/09/2006 xxx kkk yyy
2 05/09/2006 ccc kkk uuu
3 20/09/2006 xxx kkk yyy
4 05/09/2006 ccc kkk
5 20/09/2006 zzz kkk yyy
6 20/09/2006 zzz kkk
7 07/09/2006 xxx kkk uuu

Nell'esempio il documento xxx è completo, ma la dicitura va riportata
SOLO in quello della riga 3 che è il più recente mentre i documenti
ccc e zzz NON sono ancora completi ...

Grazie Norman per la tua pazienza

Riddler ?

Norman Jones

2006-09-21 09:21:03 UTC

Permalink

Ciao Riddler,

'-------------------
[...]
L mia esigenza è

Conto quante occorrenze ho di "xxx"
Se TUTTE le righe che hanno "xxx" hanno anche in un altra colonna una
cella NON vuota significa che quel documento è completo e durante la
fase di creazione dei documenti la dicitura completo va riportata SOLO
sull'ultimo quello che ha la data più recente ...

Mi sono accorto adesso che le cose si complicano ... se le date non
sono in ordine, cioè la più recente è in basso ... come posso
stabilire quelè la più recente?

Altrimenti è incompleto.

C ... H I J
1 01/09/2006 xxx kkk yyy
2 05/09/2006 ccc kkk uuu
3 20/09/2006 xxx kkk yyy
4 05/09/2006 ccc kkk
5 20/09/2006 zzz kkk yyy
6 20/09/2006 zzz kkk
7 07/09/2006 xxx kkk uuu

Nell'esempio il documento xxx è completo, ma la dicitura va riportata
SOLO in quello della riga 3 che è il più recente mentre i documenti
ccc e zzz NON sono ancora completi ...
[...]
'-------------------

(1) Dove si dovrebbe riportare "Completo"?

(2) Hai parlato del testo "xxx", ma si deve eseguire gli stessi passi
anche per ogni altro testo, i.e. "ccc", "zzz" ... ?

---
Regards,
Norman

Riddler ?

2006-09-21 10:00:02 UTC

Permalink

Post by Norman Jones
Ciao Riddler,
'-------------------
[...]
L mia esigenza è
Conto quante occorrenze ho di "xxx"
Se TUTTE le righe che hanno "xxx" hanno anche in un altra colonna una
cella NON vuota significa che quel documento è completo e durante la
fase di creazione dei documenti la dicitura completo va riportata SOLO
sull'ultimo quello che ha la data più recente ...
Mi sono accorto adesso che le cose si complicano ... se le date non
sono in ordine, cioè la più recente è in basso ... come posso
stabilire quelè la più recente?
Altrimenti è incompleto.
C ... H I J
1 01/09/2006 xxx kkk yyy
2 05/09/2006 ccc kkk uuu
3 20/09/2006 xxx kkk yyy
4 05/09/2006 ccc kkk
5 20/09/2006 zzz kkk yyy
6 20/09/2006 zzz kkk
7 07/09/2006 xxx kkk uuu
Nell'esempio il documento xxx è completo, ma la dicitura va riportata
SOLO in quello della riga 3 che è il più recente mentre i documenti
ccc e zzz NON sono ancora completi ...
[...]
'-------------------
(1) Dove si dovrebbe riportare "Completo"?

Una volta controllato che il documento sia o meno completo lo passo ad
una procedura che crea appunto le righe del documento ed appende al
fondo COMPLETO o NON COMPLETO/PARZIALE.

Post by Norman Jones
(2) Hai parlato del testo "xxx", ma si deve eseguire gli stessi passi
anche per ogni altro testo, i.e. "ccc", "zzz" ... ?

Sì, ma questo lo fa un ciclo esterno, passo man mano i documenti
xxx,ccc, ... e durante la fase dicreazioni dei documenti ( uscita,
resoconto o altro ) metto se in quel momento il documento xxx è
completo o meno.

Per il caso xxx

Passo 1 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ma è parziale il documento yyy generato
Passo 2 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ed è COMPLETO il documento yyy generato perchè fra i tre
ha la data più recente
Passo 3 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ma è parziale il documento uuu generato

Lo so sembra un pò incasinato ...

Vedila così

ho un documento in ingresso xxx_IN da questo documento vengono fatti
uscire altri documenti yyy_OUT uuu_OUT ... se tutte le occorrenze di
xxx_IN hanno un qualsiasi corrispettivo ..._OUT il documento xxx_IN è
stato esaurito quindi completo e va segnato nel documento ..._OUT con
la data più recente, altrimenti se sto facendo il documento nnn_OUT al
suo interno metto PARZIALE.

Mi basta una funzione che alla riga n mentre faccio il documento
ccc_OUT mi dica se mettere PARZIALE o COMPLETO ... poi passo alla
costruzione del documento, gia fatta, sistemata e funzionante grazie i
tuoi consigli e suggerimenti delle scorse settimane :-)

Spero di aver reso meglio l'idea ...

Grazie ancora

Riddler ?

Norman Jones

2006-09-21 13:47:26 UTC

Permalink

Ciao Ridler,

Chiedo scusa che non abbia risposto ancora - il tuo post non era visible
con OE; l'ho visto per caso con Google.

Forse potresti madarmi un esempio privato; postero' il codice
risultante in una risposta nel NG.

***@NOSPAMbtconnectDOTcom

(Cancella "NOSPAM" e sostituisci "DOT" con un punto)

---
Regards,
Norman

Riddler ?

2006-09-21 21:35:41 UTC

Permalink

Post by Norman Jones
Ciao Ridler,
Chiedo scusa che non abbia risposto ancora - il tuo post non era visible
con OE; l'ho visto per caso con Google.
Forse potresti madarmi un esempio privato; postero' il codice
risultante in una risposta nel NG.

Ma che scusa!

Non devi mica sentirti obbligato a risolvermi i problemi! .-)

Faccio un pò di prove con il codice che mi hai suggerito, io leggo i
NG con google e la tua risposta l'ho vista solo adesso, probabilmente
quando c'è un pò di ressa i post si accodano e non sono subito
visibili.

Provo e se non risolvo riposto .-)

Grazie

Riddler ?

Riddler ?

2006-09-21 13:54:13 UTC

Permalink

Post by Riddler ?

Una volta controllato che il documento sia o meno completo lo passo ad
una procedura che crea appunto le righe del documento ed appende al
fondo COMPLETO o NON COMPLETO/PARZIALE.

Post by Norman Jones
(2) Hai parlato del testo "xxx", ma si deve eseguire gli stessi passi
anche per ogni altro testo, i.e. "ccc", "zzz" ... ?

Sì, ma questo lo fa un ciclo esterno, passo man mano i documenti
xxx,ccc, ... e durante la fase dicreazioni dei documenti ( uscita,
resoconto o altro ) metto se in quel momento il documento xxx è
completo o meno.
Per il caso xxx
Passo 1 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ma è parziale il documento yyy generato
Passo 2 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ed è COMPLETO il documento yyy generato perchè fra i tre
ha la data più recente
Passo 3 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ma è parziale il documento uuu generato
Lo so sembra un pò incasinato ...
Vedila così
ho un documento in ingresso xxx_IN da questo documento vengono fatti
uscire altri documenti yyy_OUT uuu_OUT ... se tutte le occorrenze di
xxx_IN hanno un qualsiasi corrispettivo ..._OUT il documento xxx_IN è
stato esaurito quindi completo e va segnato nel documento ..._OUT con
la data più recente, altrimenti se sto facendo il documento nnn_OUT al
suo interno metto PARZIALE.
Mi basta una funzione che alla riga n mentre faccio il documento
ccc_OUT mi dica se mettere PARZIALE o COMPLETO ... poi passo alla
costruzione del documento, gia fatta, sistemata e funzionante grazie i
tuoi consigli e suggerimenti delle scorse settimane :-)
Spero di aver reso meglio l'idea ...

Dovrei fare due Find?
Una estena per il completaento e l'altra che in base alla prima mi dice
qualìè la data più recente quindi la sua riga?

Scusate la continua rottura, ma rischio di partire per la tangente e
non arrivare al punto ... :-(

Grazie ancora

Riddler ?

Norman Jones

2006-09-21 21:16:29 UTC

Permalink

Ciao Riddler,

'---------------------
Dovrei fare due Find?
Una estena per il completaento e l'altra che in base alla prima mi dice
qualìè la data più recente quindi la sua riga?

Scusate la continua rottura, ma rischio di partire per la tangente e
non arrivare al punto ... :-(

'---------------------

Prova la seguente funzione:
'=============>>
Public Function RngCompleto(Rng As Range, sStr As String) As Range
Dim RngA As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rngOut As Range
Dim rw As Range
Dim myDate As Date
Dim arr As Variant

For Each rw In Rng.Rows
rw.Select
Set rng1 = rw.Cells(1, 1) ' <<=== col C
Set rng2 = rw.Cells(1, 6) '<<=== col H
Set rng3 = rw.Cells(1, 7).Resize(1, 2) '<<=== cols I:J
If rng2.Value = sStr Then
If Application.CountA(rng3) > 0 Then
If rng1.Value > myDate Then
myDate = rng1.Value
Set rngOut = rw
End If
Else '<<=== NOM COMPLETO!
Set rngOut = Nothing
Exit Function
End If
End If
Next rw

Set RngCompleto = rngOut

End Function
'<<=============

Potresti, ad esempio, utilizzare questa funzione in questo modo:

'=============>>
Public Sub TestIt()
Dim myRng As Range

Set myRng = RngCompleto(Range("C1:J7"), "xxx")

If myRng Is Nothing Then
MsgBox "Non Completo!"
Else
MsgBox "Completo" & vbNewLine & myRng.Address(0, 0)
End If
End Sub
'<<=============

---
Regards,
Norman

Post by Riddler ?

Una volta controllato che il documento sia o meno completo lo passo ad
una procedura che crea appunto le righe del documento ed appende al
fondo COMPLETO o NON COMPLETO/PARZIALE.

Post by Norman Jones
(2) Hai parlato del testo "xxx", ma si deve eseguire gli stessi passi
anche per ogni altro testo, i.e. "ccc", "zzz" ... ?

Sì, ma questo lo fa un ciclo esterno, passo man mano i documenti
xxx,ccc, ... e durante la fase dicreazioni dei documenti ( uscita,
resoconto o altro ) metto se in quel momento il documento xxx è
completo o meno.
Per il caso xxx
Passo 1 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ma è parziale il documento yyy generato
Passo 2 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ed è COMPLETO il documento yyy generato perchè fra i tre
ha la data più recente
Passo 3 find
xxx è completo perchè per tutte le sue occorrenze presenta un altro
documento, ma è parziale il documento uuu generato
Lo so sembra un pò incasinato ...
Vedila così
ho un documento in ingresso xxx_IN da questo documento vengono fatti
uscire altri documenti yyy_OUT uuu_OUT ... se tutte le occorrenze di
xxx_IN hanno un qualsiasi corrispettivo ..._OUT il documento xxx_IN è
stato esaurito quindi completo e va segnato nel documento ..._OUT con
la data più recente, altrimenti se sto facendo il documento nnn_OUT al
suo interno metto PARZIALE.
Mi basta una funzione che alla riga n mentre faccio il documento
ccc_OUT mi dica se mettere PARZIALE o COMPLETO ... poi passo alla
costruzione del documento, gia fatta, sistemata e funzionante grazie i
tuoi consigli e suggerimenti delle scorse settimane :-)
Spero di aver reso meglio l'idea ...

Norman Jones

2006-09-21 21:27:30 UTC

Permalink

Ciao Riddler,

Post by Norman Jones
Dim RngA As Range

Post by Norman Jones
Dim arr As Variant

perché ho cambiato la funzione e queste variabili non sono utilizzate.

---
Regards,
Norman

Riddler ?

2006-09-21 21:36:08 UTC

Permalink

Post by Norman Jones
Ciao Riddler,

Post by Norman Jones
Dim RngA As Range

Post by Norman Jones
Dim arr As Variant

perché ho cambiato la funzione e queste variabili non sono utilizzate.

OK

provo

Saluti

Riddler ?

Riddler ?

2006-09-21 23:17:02 UTC

Permalink

Post by Norman Jones
'=============>>
Public Function RngCompleto(Rng As Range, sStr As String) As Range
Dim RngA As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rngOut As Range
Dim rw As Range
Dim myDate As Date
Dim arr As Variant
For Each rw In Rng.Rows
rw.Select
Set rng1 = rw.Cells(1, 1) ' <<=== col C
Set rng2 = rw.Cells(1, 6) '<<=== col H
Set rng3 = rw.Cells(1, 7).Resize(1, 2) '<<=== cols I:J
If rng2.Value = sStr Then
If Application.CountA(rng3) > 0 Then
If rng1.Value > myDate Then
myDate = rng1.Value
Set rngOut = rw
End If
Else '<<=== NOM COMPLETO!
Set rngOut = Nothing
Exit Function
End If
End If
Next rw
Set RngCompleto = rngOut
End Function
'<<=============
'=============>>
Public Sub TestIt()
Dim myRng As Range
Set myRng = RngCompleto(Range("C1:J7"), "xxx")
If myRng Is Nothing Then
MsgBox "Non Completo!"
Else
MsgBox "Completo" & vbNewLine & myRng.Address(0, 0)
End If
End Sub
'<<=============

Ciao Norman,

ho provato il tuo codice ... OK! ci siamo!

Ora determino qual'è il più recente e giocando sulla chiamata posso
ricercare su tutto il foglio ... mi sfugge però una cosa ...

C ... H I J
1 01/09/2006 xxx kkk yyy
2 05/09/2006 ccc kkk uuu
3 20/09/2006 xxx kkk ggg
4 05/09/2006 ccc kkk
5 20/09/2006 zzz kkk yyy
6 20/09/2006 zzz kkk
7 27/09/2006 xxx kkk --- <==== vuota
...
...

La colonna I ... posso anche sorvolarla ... posso fare
invece di
Set rng3 = rw.Cells(1, 7).Resize(1, 2) '<<=== cols I:J
Set rng3 = rw.Cells(1, 8) '<<=== col J

Poi come faccio a rientrare nel caso non completo senza analizzare che
rng2 non sia vuoto?
Fa tutto l'istruzione Application.CountA(rng3) > 0
Ovvero conta solo se tutti non sono vuoti, ho capito la procedura, ma
non trovo la "sottogliezza" in cui se ho 3 documenti xxx e di questi
solo due sono stati scaricati con i documenti yyy e ggg quindi è
ancora incompleto fino a che nell'esempio non riempio anche la cella J7
che avendo la data più recente verrà "etichettata" completo.

Grazie ancora

Riddler ?

Norman Jones

2006-09-22 05:57:01 UTC

Permalink

Ciao Riddler,

'-------------------
ho provato il tuo codice ... OK! ci siamo!

Ora determino qual'è il più recente e giocando sulla chiamata posso
ricercare su tutto il foglio ... mi sfugge però una cosa ...

C ... H I J
1 01/09/2006 xxx kkk yyy
2 05/09/2006 ccc kkk uuu
3 20/09/2006 xxx kkk ggg
4 05/09/2006 ccc kkk
5 20/09/2006 zzz kkk yyy
6 20/09/2006 zzz kkk
7 27/09/2006 xxx kkk --- <==== vuota
...
...

La colonna I ... posso anche sorvolarla ... posso fare
invece di
Set rng3 = rw.Cells(1, 7).Resize(1, 2) '<<=== cols I:J
Set rng3 = rw.Cells(1, 8) '<<=== col J

Poi come faccio a rientrare nel caso non completo senza analizzare che
rng2 non sia vuoto?
Fa tutto l'istruzione Application.CountA(rng3) > 0
Ovvero conta solo se tutti non sono vuoti, ho capito la procedura, ma
non trovo la "sottogliezza" in cui se ho 3 documenti xxx e di questi
solo due sono stati scaricati con i documenti yyy e ggg quindi è
ancora incompleto fino a che nell'esempio non riempio anche la cella J7
che avendo la data più recente verrà "etichettata" completo.

'-------------------

Prova la seguente versione della funzione:

'=============>>
Public Function RngCompleto(Rng As Range, sStr As String) As Range
Dim RngA As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rngOut As Range
Dim rw As Range
Dim myDate As Date

For Each rw In Rng.Rows
Set rng1 = rw.Cells(1, 1) ' col C
Set rng2 = rw.Cells(1, 6) 'col H
Set rng3 = rw.Cells(1, 7).Resize(1, 2) 'cols I:J
If rng2.Value = sStr Then
If Application.CountA(rng3) = 2 Then
If rng1.Value > myDate Then
myDate = rng1.Value
Set rngOut = rw
End If
Else '<<=== NON COMPLETO!
Set rngOut = Nothing
Exit Function
End If
End If
Next rw

Set RngCompleto = rngOut

End Function

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

Public Sub TestIt()
Dim myRng As Range

Set myRng = RngCompleto(Range("C1:J7"), "xxx")

If myRng Is Nothing Then
MsgBox "Non Completo!"
Else
MsgBox "Completo" & vbNewLine & myRng.Address(0, 0)
End If
End Sub
'<<=============

---
Regards,
Norman

Norman Jones

2006-09-22 06:07:42 UTC

Permalink

Ciao Riddler,

Sostituisci il mio codice con:

'=============>>
Public Function RngCompleto(Rng As Range, sStr As String) As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rngOut As Range
Dim rw As Range
Dim myDate As Date

For Each rw In Rng.Rows
Set rng1 = rw.Cells(1, 1) '<<=== col C
Set rng2 = rw.Cells(1, 6) '<<=== col H
Set rng3 = rw.Cells(1, 7).Resize(1, 2) '<<=== cols I:J
If rng2.Value = sStr Then
If Application.CountA(rng3) = 2 Then
If rng1.Value > myDate Then
myDate = rng1.Value
Set rngOut = rw
End If
Else '<<=== NON COMPLETO!
Set rngOut = Nothing
Exit Function
End If
End If
Next rw

Set RngCompleto = rngOut

End Function

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

Public Sub TestIt()
Dim myRng As Range

Set myRng = RngCompleto(Range("C1:J7"), "xxx")

If myRng Is Nothing Then
MsgBox "Non Completo!"
Else
MsgBox "Completo" & vbNewLine & myRng.Address(0, 0)
End If
End Sub
'<<=============

---
Regards,
Norman

Riddler ?

2006-09-22 06:50:45 UTC

Permalink

Post by Norman Jones
Ciao Riddler,

Ciao Norman, anche tu dopo una nottataccia già al lavoro eh? :-)
[...]

Post by Norman Jones
Set rng3 = rw.Cells(1, 7).Resize(1, 2) '<<=== cols I:J <---------- PUNTO 1
If rng2.Value = sStr Then
If Application.CountA(rng3) = 2 Then <---------- PUNTO 2
If rng1.Value > myDate Then
myDate = rng1.Value
Set rngOut = rw
End If

[...]

Punto1
Set rng3 = rw.Cells(1, 7).Resize(1, 2)

E se selezionassi "solo" la J comprometterebbe il funzionamento ...
provo ...

Punto 2
If Application.CountA(rng3) = 2

Allora è questa la sottogliezza?
Mi dicequanti valori sono non vuoti? ( perà la colonna I non va
contata nel conteggio ... )

vado a provare ...

Grazie ancora!

Riddler ?

elby

2006-09-23 01:27:52 UTC

Permalink

Post by Riddler ?

Post by Norman Jones
Ciao Riddler,

Ciao Norman, anche tu dopo una nottataccia già al lavoro eh? :-)
[...]

[...]
Punto1
Set rng3 = rw.Cells(1, 7).Resize(1, 2)
E se selezionassi "solo" la J comprometterebbe il funzionamento ...
provo ...
Punto 2
If Application.CountA(rng3) = 2
Allora è questa la sottogliezza?
Mi dicequanti valori sono non vuoti? ( perà la colonna I non va
contata nel conteggio ... )
vado a provare ...
Grazie ancora!
Riddler ?

Ciao Riddler ?
Se ho ben capito quello che vuoi ottenere è un 'marker' in
corrispondenza delle righe con data più recente di CodiceIn dove
CodiceOut1 e CodiceOut2 non sono nulli. Vi è allora una funzione di
aggregazione ( MAX(<campo data>) con raggruppamento per codice ( GROUP
BY <campo CodiceIN>). Questa selezione deve essere però filtrata (
clausola WHERE ) con un criterio in base al quale il codice non deve
appartenere ( NOT IN )
all'elenco delle pratiche incomplete. Questa è una subquery nidificata
nella query principale: selezionami i codiciIN dove anche uno dei
codiciOUT è nullo.

In una copia della tua cartella denomina i tuoi dati (Colonna A:D),
comprensivi della prima riga con i nomi delle colonne, con il nome
'Dati' nella finestra Definisci Nomi. I nomi dei campi ( prima riga )
devono essere: Data; CodiceIN; CodiceOUT1; CodiceOUT2.
Con attivo il foglio2 vuoto lancia da un modulo questa routine:

Sub TestQuery()
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DBQ=" &
ThisWorkbook.FullName _
& ";DefaultDir=" & ThisworkbookPath & "\" _
& "DMN\Documenti;Driver={Driver do Microsoft Excel(*.xls)};" _
& "DriverId=790;FIL=excel
8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;" _
&
"ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",
_
Destination:=Range("A1"))
.CommandText = _
"SELECT MAX(Dati.Data ) , Dati.CodiceIN" _
& vbCrLf & "FROM `C:\DatiOrigine`.Dati Dati" _
& vbCrLf & "WHERE (Dati.CodiceIN Not In" & vbCrLf & _
" (SELECT Dati.CodiceIN" & vbCrLf & "FROM `C:\DatiOrigine`.Dati
Dati" _
& vbCrLf & "WHERE (Dati.CodiceOUT2 Is Null) OR " _
& "(Dati.CodiceOUT1 Is Null)))" & vbCrLf & _
"GROUP BY Dati.CodiceIN;"
.Name = "StatoPratiche"
.FillAdjacentFormulas = True
.Refresh BackgroundQuery:=False
End With
End Sub

Dovresti ottenere una tabella a 2 colonne ( già denominata
StatoPratiche); inserisci nella colonna C la formula da C2 in poi
=CONCATENA(A2;B2). Ottieni un codice univoco.
Nel foglio dove hai i dati di origine, inserisci nella cella E2 la
seguente formula (limitata per esemplificazione solo alle prime 9 righe
di dati) e copiala fino alla fine dei dati:
=SE(VAL.NON.DISP(CONFRONTA(CONCATENA(A3;B3);Foglio2!$C$2:$C$10;0))=FALSO;"COMPLETO";SE(VAL.NON.DISP(CONFRONTA(B3;Foglio2!$B$2:$B$10;0))=FALSO;"";"NON
COMPLETO"))
Nel caso accodi nuovi dati aggiorna l'indirizzo dell'intervallo
denominato 'Dati'; sia in questo caso che dopo semplice modifica dei
dati esistenti aggiorna la query sul foglio 2 dalla barra flottante che
dovrebe essere visibile ( pulsante con punto esclamativo rosso ).
So che la logica SQL è ostica ma rincorrere laboriosi algoritmi in
puro VBA per ottenere un risultato analogo ma poco riciclabile non è a
mio avviso una strada consigliabile.
Ciao Elio

elby

2006-09-23 01:34:15 UTC

Permalink

Post by elby

Post by Riddler ?

Post by Norman Jones
Ciao Riddler,

Ciao Norman, anche tu dopo una nottataccia già al lavoro eh? :-)
[...]

[...]
Punto1
Set rng3 = rw.Cells(1, 7).Resize(1, 2)
E se selezionassi "solo" la J comprometterebbe il funzionamento ...
provo ...
Punto 2
If Application.CountA(rng3) = 2
Allora è questa la sottogliezza?
Mi dicequanti valori sono non vuoti? ( perà la colonna I non va
contata nel conteggio ... )
vado a provare ...
Grazie ancora!
Riddler ?

Ciao Riddler ?
Se ho ben capito quello che vuoi ottenere è un 'marker' in
corrispondenza delle righe con data più recente di CodiceIn dove
CodiceOut1 e CodiceOut2 non sono nulli. Vi è allora una funzione di
aggregazione ( MAX(<campo data>) con raggruppamento per codice ( GROUP
BY <campo CodiceIN>). Questa selezione deve essere però filtrata (
clausola WHERE ) con un criterio in base al quale il codice non deve
appartenere ( NOT IN )
all'elenco delle pratiche incomplete. Questa è una subquery nidificata
nella query principale: selezionami i codiciIN dove anche uno dei
codiciOUT è nullo.
In una copia della tua cartella denomina i tuoi dati (Colonna A:D),
comprensivi della prima riga con i nomi delle colonne, con il nome
'Dati' nella finestra Definisci Nomi. I nomi dei campi ( prima riga )
devono essere: Data; CodiceIN; CodiceOUT1; CodiceOUT2.
Sub TestQuery()
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DBQ=" &
ThisWorkbook.FullName _
& ";DefaultDir=" & ThisworkbookPath & "\" _
& "DMN\Documenti;Driver={Driver do Microsoft Excel(*.xls)};" _
& "DriverId=790;FIL=excel
8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;" _
&
"ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",
_
Destination:=Range("A1"))
.CommandText = _
"SELECT MAX(Dati.Data ) , Dati.CodiceIN" _
& vbCrLf & "FROM `C:\DatiOrigine`.Dati Dati" _
& vbCrLf & "WHERE (Dati.CodiceIN Not In" & vbCrLf & _
" (SELECT Dati.CodiceIN" & vbCrLf & "FROM `C:\DatiOrigine`.Dati
Dati" _
& vbCrLf & "WHERE (Dati.CodiceOUT2 Is Null) OR " _
& "(Dati.CodiceOUT1 Is Null)))" & vbCrLf & _
"GROUP BY Dati.CodiceIN;"
.Name = "StatoPratiche"
.FillAdjacentFormulas = True
.Refresh BackgroundQuery:=False
End With
End Sub
Dovresti ottenere una tabella a 2 colonne ( già denominata
StatoPratiche); inserisci nella colonna C la formula da C2 in poi
=CONCATENA(A2;B2). Ottieni un codice univoco.
Nel foglio dove hai i dati di origine, inserisci nella cella E2 la
seguente formula (limitata per esemplificazione solo alle prime 9 righe
=SE(VAL.NON.DISP(CONFRONTA(CONCATENA(A3;B3);Foglio2!$C$2:$C$10;0))=FALSO;"COMPLETO";SE(VAL.NON.DISP(CONFRONTA(B3;Foglio2!$B$2:$B$10;0))=FALSO;"";"NON
COMPLETO"))
Nel caso accodi nuovi dati aggiorna l'indirizzo dell'intervallo
denominato 'Dati'; sia in questo caso che dopo semplice modifica dei
dati esistenti aggiorna la query sul foglio 2 dalla barra flottante che
dovrebe essere visibile ( pulsante con punto esclamativo rosso ).
So che la logica SQL è ostica ma rincorrere laboriosi algoritmi in
puro VBA per ottenere un risultato analogo ma poco riciclabile non è a
mio avviso una strada consigliabile.
Ciao Elio

P.S.
Non ho reso dinamica l'origine dati nella istruzione SQL di CommandText
della QueryTable. Pertanto perchè la cosa funzioni metti il file in C:
e chiamalo OrigineDati.
Ciao Elio