[Excel] Conta.se in versione VBA

[mpsinf]

Codice: Seleziona tutto

 Dim offerenti As Range
 Dim chioffre As Range

 Set chioffre = Range("H:H")
 chioffre.Clear

 Set offerenti = Range("C:C")

For Each cl In offerenti
 If cl.Value = "" Then  'se nome è vuoto allora si ferma uscendo dal ciclo For
    Exit For
    Else
   If Application.CountIf(chioffre, cl.Value) = 0 Then  ' se CountIf restituisce zero (quindi "nome" non è presente), allora
    cellavuota = cellavuota + 1
    Cells(cellavuota, 8) = cl.Value  'e scriviamo "nome dell'offerente" nella colonna H
   End If
  End If
 
Next
 
 ' riordina celle
 Range("H:J").Select
 Selection.Sort Key1:=Range("I1"), Order1:=xlDescending, Header:=xlNo, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers
End With

Con questo codice analizzando i dati delle due colonne C e D così composte
pippo___0,6
pluto____0,54
pippo____0,32
minni____0,23
gastone__0,22
minni____0,19

ricreo una colonna H:H con i singoli nomi... inoltre sul foglio nella colonna I:I ho la formula =CONTA.SE(INDIRETTO("$C$1:$C$10000");H1) copiata per n. righe (che mi conteggia quante volta è presente il nome in H1..Hn) così pure la colonna J riporta =CERCA.VERT(H1;C:D;2;0) che mi cerca il primo valore in colonna D del nome H1

Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?

[scossa]

ricreo una colonna H:H con i singoli nomi... inoltre sul foglio nella colonna I:I ho la formula =CONTA.SE(INDIRETTO("$C$1:$C$10000");H1) copiata per n. righe (che mi conteggia quante volta è presente il nome in H1..Hn) così pure la colonna J riporta =CERCA.VERT(H1;C:D;2;0) che mi cerca il primo valore in colonna D del nome H1

Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?

A dire il vero sia il codice che le formule utilizzate (che senso ha usare INDIRETTO("$C$1:$C$10000")....) mi lasciano alquanto perplesso.
Puoi allegare il tuo file?

[mpsinf]

Indiretto l'avevo usato perchè, se in C viene inserito un dato, con l'abbassamento della riga verso il basso, l'intervallo rimane costante.
Inviare il file non ha molto senso visto che è composto da 2 colonne, e devo fare il conteggio delle volte che è presente un determinato nome in una lista.

[Anthony47]

Se vuoi inserire quelle formule in I1 e J1 userai le istruzioni

Codice: Seleziona tutto

    Range("I1").Formula = "=COUNTIF(INDIRECT(""$C$1:$C$10000""),H1)"
    Range("J1").Formula = "=VLOOKUP(D1,A:A,1,0)"


Poi calcoli nella variabile LastR l' ultima riga su cui devi ricopiare le formule; ad es

Codice: Seleziona tutto

LastR=Cells(Rows.Count, "A").End(Xlup).Row   'Calcolo fatto su colonna A


E infine trascini I1:J1 con l' istruzione

Codice: Seleziona tutto

    Range("I1:J1").AutoFill Destination:=Range("I1:J" & LastR), Type:=xlFillDefault


Ciao

[Flash30005]

Ma forse ti occorre una macro così?

Codice: Seleziona tutto

Sub CreaEleConta()
UR1 = Range("C" & Rows.Count).End(xlUp).Row
For RR1 = 1 To UR1
Tr = 0
UR2 = Range("H" & Rows.Count).End(xlUp).Row + 1
    For RR2 = 1 To UR2
    If Range("H" & RR2).Value = Range("C" & RR1).Value Then
        Range("I" & RR2).Value = Range("I" & RR2).Value + Range("D" & RR1).Value
        Tr = 1
    End If
    Next RR2
    If Tr = 0 Then
    Range("H" & UR2).Value = Range("C" & RR1).Value
    Range("I" & UR2).Value = Range("I" & UR2).Value + Range("D" & RR1).Value
    End If
Next RR1
End Sub

in "H" crea l'elenco nomi unici e in "I" fa la somma corrispondente al nome trovato

Ciao


EDIT: C'è un messaggio di Anthony prima di questo

[mpsinf]

Wow quante istruzioni nuove da imparare e collaudare!! Grazie ad entrambi ...vamos al collaudo

[scossa]

Indiretto l'avevo usato perchè, se in C viene inserito un dato, con l'abbassamento della riga verso il basso, l'intervallo rimane costante.

????
mi spieghi la differenza tra
=CONTA.SE(INDIRETTO("$C$1:$C$10000");H1)
e
=CONTA.SE($C$1:$C$10000;H1)

[mpsinf]

Se in C viene inserito un dato, con un copia incolla con l'abbassamento della riga verso il basso copiata da una macro che elenca valori in sequenza =CONTA.SE($C$1:$C$10000;H1) diventa =CONTA.SE($C$2:$C$10001;H1) con indiretto invece l'intervallo rimane sempre da 1 a 10.000

[scossa]

Se in C viene inserito un dato, con un copia incolla con l'abbassamento della riga verso il basso copiata da una macro che elenca valori in sequenza =CONTA.SE($C$1:$C$10000;H1) diventa =CONTA.SE($C$2:$C$10001;H1) con indiretto invece l'intervallo rimane sempre da 1 a 10.000

Ah, ok ora ho capito cosa intendevi.
Grazie.

[scossa]

Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?

Questo codice dovrebbe fare quello che chiedi:

Codice: Seleziona tutto

Public Sub prova()
  Dim ws As Worksheet
  Dim offerenti As Range
  Dim chioffre As Range
  Dim cl As Range
  Dim cellavuota As Long

  Set ws = ThisWorkbook.ActiveSheet
  Set chioffre = ws.Range("H1")
  Set offerenti = ws.Range("C1").CurrentRegion
 
  chioffre.CurrentRegion.Clear
 
  For Each cl In offerenti.Resize(, 1)
    If Application.CountIf(chioffre, cl.Value) = 0 Then 
      cellavuota = cellavuota + 1
      chioffre.Cells(cellavuota).Value = cl.Value
      Set chioffre = chioffre.CurrentRegion
    End If
  Next
 
  For Each cl In chioffre
    cl.Offset(0, 1) = Application.CountIf(offerenti.Columns(1), cl)
    cl.Offset(0, 2) = Application.VLookup(cl, offerenti, 2, 0)
  Next
 
 
  ' riordina celle
  Set chioffre = chioffre.CurrentRegion
  chioffre.Sort Key1:=chioffre(1, 2), Order1:=xlDescending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  DataOption1:=xlSortTextAsNumbers
 
  Set chioffre = Nothing
  Set offerenti = Nothing
  Set ws = Nothing
End Sub

[scossa]

Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?

Questo codice dovrebbe fare quello che chiedi:

Ovviamente dando per scontato che le colonn B E G K siano vuote, altrimenti devi circoscrivere UsedRange.

[mpsinf]

Pur apprezzando lo sforzo e l'insegnamento sul Region ho risolto con il tuo aiuto in maniera un po' più spartana visto che nelle altre colonne c'erano altri dati ma non utili.

Codice: Seleziona tutto

Sub A3_Rush()
With Worksheets("Rush")
 Dim offerenti As Range
 Dim chioffre As Range

 Range("H:J").Clear
 
 Set chioffre = Range("H:H")
 Set offerenti = Range("C:C")

For Each cl In offerenti
 If cl.Value = "" Then  'se nome è vuoto allora si ferma uscendo dal ciclo For
    Exit For
    Else
   
   If Application.CountIf(chioffre, cl.Value) = 0 Then 
    cellavuota = cellavuota + 1
    Cells(cellavuota, 8) = cl.Value  'e scriviamo "nome dell'offerente" nella colonna H
   End If
 End If
Next
 
Set offerenti = Range("C1:D" & Cells(Rows.Count, "D").End(xlUp).Row)

For Each cl In chioffre
    cl.Offset(0, 1) = Application.CountIf(offerenti.Columns(1), cl)
    cl.Offset(0, 2) = Application.VLookup(cl, offerenti, 2, 0)
    i = i + 1
    If i = .Cells(Rows.Count, "H").End(xlUp).Row Then Exit For
Next
 
 ' riordina celle per n. di offerte
 Range("H:J").Select
 Selection.Sort Key1:=Range("I1"), Order1:=xlDescending, Header:=xlNo, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers
End With
End Sub

[Flash30005]

Ma nella colonna "J" non volevi i totali dei singoli nomi?
Nella macro da me postata questo lo ottenevi nella colonna I (ma non hai detto nulla a riguardo)
ora, visto che ti occorre anche contare le presenze (in I) ho implementato la stessa macro per ottenere i risultati corretti

Codice: Seleziona tutto

Sub CreaEleConta()
UR1 = Range("C" & Rows.Count).End(xlUp).Row
 Range("H:J").Clear
For RR1 = 1 To UR1
Tr = 0
UR2 = Range("H" & Rows.Count).End(xlUp).Row + 1
    For RR2 = 1 To UR2
    If Range("H" & RR2).Value = Range("C" & RR1).Value Then
        Range("I" & RR2).Value = Range("I" & RR2).Value + 1
        Range("J" & RR2).Value = Range("J" & RR2).Value + Range("D" & RR1).Value
        Tr = 1
    End If
    Next RR2
    If Tr = 0 Then
    Range("H" & UR2).Value = Range("C" & RR1).Value
    Range("I" & UR2).Value = 1
    Range("J" & UR2).Value = Range("D" & RR1).Value
    End If
Next RR1
 Range("H:J").Select
 Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlNo, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers
Range("H1").Select
End Sub

Ciao

[mpsinf]

Alla fine ho scelto il tuo codice Flash30005 in quanto molto breve e lineare

Ho solo tolto la seguente riga

Codice: Seleziona tutto

Range("J" & RR2).Value = Range("J" & RR2).Value + Range("D" & RR1).Value


perchè ha me interessava solo il primo valore trovato e non la somma dei risultati

e unico neo l'elencazione parte dalla 2a riga ma poi il comando dati ordina nella parte finale mette tutto a posto..

Grazie anche agli altri perchè credo che questo post faccia parte di quegli esempi di come uno stesso caso con risultati quasi uguali che portano allo stesso obiettivo, si possa affrontare in maniera con approcci differenti.

[mpsinf]

Nelle varie esperienze di VBA mi sono accorto che per la selezione di n. righe si usano in VBA dei Rows.Count, ma volevo chiedere la differenza sostanziale o solo formale che ci sono tra questi 2 cicli for

Codice: Seleziona tutto

With Worksheets(Sheets(1).Name)

For n = 1 To .Range("K" & Rows.Count).End(xlUp).Row

For n = 1 To .Cells(Rows.Count, "K").End(xlUp).Row


[scossa]

Nelle varie esperienze di VBA mi sono accorto che per la selezione di n. righe si usano in VBA dei Rows.Count, ma volevo chiedere la differenza sostanziale o solo formale che ci sono tra questi 2 cicli for

Codice: Seleziona tutto

With Worksheets(Sheets(1).Name)

For n = 1 To .Range("K" & Rows.Count).End(xlUp).Row

For n = 1 To .Cells(Rows.Count, "K").End(xlUp).Row


Non so dove hai trovato With Worksheets(Sheets(1).Name) ma non ha senso, meglio:
With Worksheets(1) che però è dipendente dall'ordine in cui hai i fogli nella cartella, o meglio ancora
With Worksheets("Nome del foglio")

Riguardo alle due notazioni: sono equivalenti, la prima fa riferimento all'oggetto Range (di cui specifica una solo cella), il secondo alla proprietà Cells dell'oggetto Worksheet.

[scossa]

Alla fine ho scelto il tuo codice Flash30005 in quanto molto breve e lineare

Solo chiarezza, senza spirito di polemica (non me ne voglia Flash): se si omettono le dichiarazioni delle variabili, la distruzione delle istanze degli oggetti (set = ... nothing), i commenti e le righe vuote (che rendono più leggibile il codice) vedrai che anche il mio codice diventa breve ed è assolutamente lineare.

[mpsinf]

Se vuoi inserire quelle formule in I1 e J1 userai le istruzioni

Codice: Seleziona tutto

    Range("I1").Formula = "=COUNTIF(INDIRECT(""$C$1:$C$10000""),H1)"
    Range("J1").Formula = "=VLOOKUP(D1,A:A,1,0)"


Quando eseguo la riga di codice

Codice: Seleziona tutto

Range("B4").Formula = "=CONTA.SE(K:K;" >= " & B2-2)"

in B4 mi appare VERO invece del numero del conteggio
selezionando la cella B4 NON mi ritrovo la formula al suo interno come posso fare per inserirla?

Inoltre volevo sapere se c'era un modo per evitare il copia incolla (del codice qui sotto), in quanto facendo girare la macro sul PC dove lavoro, quando uso il copia incolla in un altro programma mi ritrovo la clipboard contenente i dati che sta copiando la macro... (a dire il vero mi succede addirittura da altro PC collegato solo con un KVM ?? ) ed anche perchè ad ore di distanza di esecuzione mi da un errore di invalid Range alla riga di insert

Codice: Seleziona tutto

If .Cells(a, "E").Value <> "" And .Cells(a, "E").Value > .Range("L1").Value Then
     .Range("D" & a & ":H" & a).Copy 'brutto codice a vedersi
     .Range("K1:O1").Insert Shift:=xlDown  '  [b]qui alle volte va in errore[/b]
   End If

[mpsinf]

Codice: Seleziona tutto

Range("B4").Formula = "=MAX(O:O)"

funziona

Codice: Seleziona tutto

Range("B4").Formula = "=CONTA.SE(K:K;" >= " & B2-2)"

NO !!
la questione mi pare di capire sia sulle ""

[mpsinf]

Codice: Seleziona tutto

Range("B4").FormulaLocal = "=CONTA.SE(K:K;"">="" & B2-2)"

con questa è andata !! mistero o