arenato sul confronto due colonne di valori con VBA

[marcus69]

confronto tra due colonne

buongiorno, ho due serie di dati nello stesso foglio ( in futuro saran due foglii diversi, ma per capire il codice mi vien comodo lasciar sullo stesso foglio)

il file deve confrontare due giacenze di magazzino

nella serie di sinistra ho la giacenza nuova ( che sarà il foglio che esporto in futuro )
nella serie di destra ho la giacenza in essere ( che sarà il mio foglio di lavoro )

le serie sono composte da articolo, nome e qta

scrivere il codice per copiare i dati facendo il confronto articolo, è stato semplice anche per me, quindi vuol dire che era proprio una stupidata.

ora ; se nella serie nuova ( sulla sinistra nel mio foglio , in futuro foglio che esporto dal database ) ho un articolo ( o n articoli ) in più, devo vederlo nella serie di destra ( il mio futuro foglio di lavoro )

problema, NON E' DETTO che gli articoli siano inseriti in fondo alla lista, potrebbero essere in mezzo.
stiamo parlando di 1500 articoli, che se diventano 1501 come trovo quello nuovo?

Stavo provando a modificare il mio codice, per trovare l'articolo della serie di sinistra e aggiungerlo in fondo alla serie di destra ma mi sono arenato.
Ho provato invenzioni tipo " if exist" "if not", la funzione find. ma non sono stato capace di trovare nulla
la logica di lavoro deve essere
" se nella colonna A c'è un valore che non esiste nella colonna F, nella prima riga libera della colonna F devi mettere, articolo, nome e qta" .
in pratica aggiungere la riga articolo incrementando il mio file base di lavoro e controllo.
p.s. non fate caso ai contatori, è un mio riferimento quando facevo le prove per essere sicuro che il codice girasse ogni volta
le righe in colonna J semplicemente restituiscono i valori della riga libera/occupata del range di controllo

allego file
http://www.filedropper.com/quindi


e qui di seguito il codiceSub copiagiacenza()
Cells(29, 9) = Cells(29, 9) + 1

Dim numriga As Long
Dim numriga1 As Long

Dim J As Long
numriga = Sheets("uno").Range("f" & Rows.Count).End(xlUp).Row
numriga1 = Sheets("uno").Range("f" & Rows.Count).End(xlUp).Row + 1 'numero prima riga libera
Cells(1, 10) = numriga
Cells(2, 10) = numriga1

Sheets("uno").Select
a = 2
b = 2
For a = 2 To 10
If Cells(a, 1) = "" Then a = 10
For b = 2 To 10
If Cells(b, 6) = Cells(a, 1) Then ' se la cella in colonna a è uguale alla cella in colon f
Cells(b, = Cells(a, 3) ' 'la colonna h erdita il valore dlela colonna c
End If
If flag = 1 Then Cells(27, 10) = " un altro passo"
Next b
Next a
Cells(30, 9) = Cells(30, 9) + 1
Cells(29, 10) = Cells(29, 10) + 1

Cells(1, 10) = numriga
Cells(2, 10) = numriga1

Cells(30, 10) = Cells(30, 10) + 1
End Sub

[marcus69]

ed ecco la soluzione

viewtopic.php?f=26&t=111098


per dai adesso aiutami a capire, riesco a leggere tutto il codice, ma voglio capire ne ho bisogno...

Dim RPos As String, myMatch, I As Long ' dichiara la variabile RPos come una stringa , perché è necessario? tutto il resto dell'istruzione a cosa serve?
Dim BTab As String, J As Long, myTim As Single ' idem come sopra...
Range(RPos).CurrentRegion.ClearContents ', ok pulisci l'area dove vai a scrivere. Perché? se gli articoli son quelli, perché cancellarli?
Range(RPos).Resize(1, 2) = Array("articolo", "qta") ' la butto li : stai dichiarando che il range di celle ha una dimensione di 1 riga e due colonne, ed è identificato dai valori dell array che dichiari dopo "="
For I = 2 To Range(BTab).Offset(Rows.Count - 10, 0).End(xlUp).Row ' perche mandi la riga indietro di 10 posti rispetto alle celle che hai contato?
myMatch = Application.Match(Cells(I, "A").Value, Range(RPos).Resize(J, 1), False) ' ok fai fare il match e poi con l'if gli dici se non trovi aggiungi, stando sopra l'ultima cella vuota. Perché il -1 nell offset? Il rowcount non restituisce l'ultima cella vuota della colonna già?
Range(RPos).Offset(J - 1, 0).Value = Cells(I, "A").Value ' perch gli dici di stare una riga sopra con l'offset, non dovrebbe scrivere sulla prima riga libera? lo fa, ma non capisco perchè
lo leggo... ma ho questi dubbi.... se hai voglia e tempo...

[marcus69]

il problema di questa soluzione è che "riscrive" la giacenza.
quindi i dati precedenti vengono sovra scritti

serve che i dati precedenti non vengano toccati, ma solo aggiornati e vengano aggiunti gli articoli nuovi senza perdere lo storico

[Anthony47]

Ieri pomeriggio avevo rapidamente letto il tuo quesito e mi era subito parso evidente che si potesse sfruttare la macro che poi hai usato; ma non avevo tempo per articolare una risposta e vedo con piacere che ti sei arrangiato da solo, bravo.

Innanzitutto rispondo ai dubbi che l'interpretazione del codice ti lasciano:

Dim RPos As String, myMatch, I As Long ' dichiara la variabile RPos come una stringa , perché è necessario? tutto il resto dell'istruzione a cosa serve?
Dim BTab As String, J As Long, myTim As Single ' idem come sopra...

Con queste istruzioni "dichiaro" le variabili che uso nel codice e la loro tipologia; quindi RPos e' una String; myMatch deve rimanere una Variant perche' potra' contenere numeri o errori; I e' un numero Long; e cosi' via. Posso inserire piu' dichiarazioni nella stessa istruzione Dim.
Dichiarare le variabili in genere non e' indispensabile, ma aiuta a non commettere errori di battitura (se hai impostato Option Explicit in testa al modulo) e aiuta il vba ad allocare in modo preventivo le risorse necessarie al codice.
Per l'uso di Dim: https://docs.microsoft.com/it-it/office ... -statement
Per le tipologie di dati: https://docs.microsoft.com/it-it/office ... pe-summary

Range(RPos).CurrentRegion.ClearContents ', ok pulisci l'area dove vai a scrivere. Perché? se gli articoli son quelli, perché cancellarli?

La macro da cui sei partito ha l'obiettivo di creare un indice e sommare le quantita', e non sa se le voci gia' presenti nell'area dell'indice sono ancora presenti nel nuovo elenco e soprattutto non sa a quali righe le quantita' calcolate sono riferite. Quindi fa tabula rasa e crea un indice affidabile

Range(RPos).Resize(1, 2) = Array("articolo", "qta") ' la butto li : stai dichiarando che il range di celle ha una dimensione di 1 riga e due colonne, ed è identificato dai valori dell array che dichiari dopo "="

No, sto scrivendo le intestazioni delle due colonne

For I = 2 To Range(BTab).Offset(Rows.Count - 10, 0).End(xlUp).Row ' perche mandi la riga indietro di 10 posti rispetto alle celle che hai contato?

Rows.Count mi restituisce il numero di righe presenti nel foglio; usandolo come "Offset" rispetto alla base della tabella devo evitare di "sballare" (andare oltre il limite di righe presenti) e con "-10" compenso il possibile inizio della base fino a riga 10.

myMatch = Application.Match(Cells(I, "A").Value, Range(RPos).Resize(J, 1), False) ' ok fai fare il match e poi con l'if gli dici se non trovi aggiungi, stando sopra l'ultima cella vuota. Perché il -1 nell offset? Il rowcount non restituisce l'ultima cella vuota della colonna già?
Range(RPos).Offset(J - 1, 0).Value = Cells(I, "A").Value ' perch gli dici di stare una riga sopra con l'offset, non dovrebbe scrivere sulla prima riga libera? lo fa, ma non capisco perché

Nella macro, se myMatch e' in errore (valore non trovato) uso Offset(J - 1, 0) e uso invece Offset(myMatch - 1, 1) se myMatch trova una corrispondenza. J e' un contatore di "riga su cui devo scrivere", myMatch e' il numero di riga (nella tabella in cui ho cercato) su cui quel valore e' gia' presente; usati come "offset" rispetto al punto di partenza il loro valore deve essere decrementato di 1.
Per l'uso di Offset: https://docs.microsoft.com/it-it/office ... nge.offset


Quanto al tuo problema, vedo che hai utilizzato quanto proposto a W&G (la Sub Riepiloga) per ricreare integralmente la "giacenza nuova"; dovevi aspettarti che creando l'elenco da zero avresti avuto solo le quantita' provenienti dalla tabella nuova.
Questa macro invece aggiunge a un elenco Vecchio le voci presenti in un Elenco Nuovo ma mancanti nel Vecchio:

Codice: Seleziona tutto

Sub AddItems()
Dim taNew As Range, taOld As Range, LastN As Long, LastO As Long, myMatch
Dim I As Long, J As Long, taWidth, Mess As String
'
Set taNew = Sheets("uno").Range("A1")   '<<< La tabella Nuova (Foglio e Range)
Set taOld = Sheets("uno").Range("J1")   '<<< La tabella Precedente (Foglio e Range)
taWidth = 3                             '<<< N° di colonne in tabella
'
LastN = taNew.Offset(Rows.Count - 100, 0).End(xlUp).Row     'Ultima riga Nuovo elenco
LastO = taOld.Offset(Rows.Count - 100, 0).End(xlUp).Row     'Ultima riga Vecchio elenco
For I = taNew.Row To LastN                                  'Confronta ogni riga del nuovo elenco col vecchio
    myMatch = Application.Match(taNew.Cells(I, 1), taOld.Resize(LastO + J, 1), False)
    If IsError(myMatch) Then                                'Voce mancante in elenco?
        'Aggiungila:
        taOld.Offset(LastO + J).Resize(1, taWidth).Value = taNew.Cells(I, 1).Resize(1, taWidth).Value
        J = J + 1               'conta linee aggiunte
    End If
Next I
'Messaggio finale:
If J = 0 Then
    Mess = "Nessuna aggiunta"
Else
    Mess = "Aggiunte n. " & J & " righe"
End If
MsgBox (Mess)
End Sub

Le istruzioni marcate <<< sono da adattare alla tua situazione, anche aiutandoti con i ricchi commenti...

Ciao

[marcus69]

beh, dirti grazie, come sempre è solo riduttivo.
ma devi anche sentirti in colpa, sto spendendo la domenica a capire ogni singola istruzione del codice che mi hai postato.
qualcosa inizio a fare.... poca roba, pochi cicli ma alcune cose mi escono.

l'altro giorno sono stato in grado di fare la mia prima routine da solo, una cosa semplice pescare dati da più fogli con i cerca vert in via e aggregarli, ma è stata una soddisfazione

[marcus69]

quando dichiari e allochi le variabili con DIM
Dim taNew As Range, taOld As Range, LastN As Long, LastO As Long, myMatch
Dim I As Long, J As Long, taWidth, Mess As String

myMatch e taWidth non li dichiari perché li lasci " variant " di default?

LastN = taNew.Offset(Rows.Count - 100, 0).End(xlUp).Row qui, seguendo il ragionamento della tua risposta per non rischiare di sballare, avendo 1500 articoli piu o meno in magazzino, aumento a -2000 il conteggio delle righe?

taOld.Offset(LastO + J).Resize(1, taWidth).Value = taNew.Cells(I, 1).Resize(1, taWidth).Value ''qui gli dici di aggiungere la riga, ma non capisco l'istruzione RESIZE, scusa stai puntando la riga, tramite offset la sposti (1, taWidth), ma proprio non capisco perché?

[Anthony47]

Scordati che mi vengano sensi di colpa...

Per il significato e l'uso di Resize: https://docs.microsoft.com/it-it/office ... nge.resize
Nel codice che citi, resize serve ad allargare l'intervallo fino a 3 colonne.

LastN = taNew.Offset(Rows.Count - 100, 0).End(xlUp).Row

Ti faccio un esempio:
-se TaNew fosse posizionata in A10, se comandassi un Offset di un numero di righe pari a Rows.count andrei 10 righe oltre la capacita' di Excel; volessi quindi essere certo di partire dall'ultima riga del foglio per il comando End(xlUp) dovrei usare

Codice: Seleziona tutto

LastN = taNew.Offset(Rows.Count - taNew.Row, 0).End(xlUp).Row

Per il significato di End: https://docs.microsoft.com/it-it/office ... .range.end

SEMPLIFICANDO, e dando per scontato (1) che i dati utente conterranno molte meno del max numero di righe del foglio e (2) che nessuno fa partire una tabella oltre la seconda/decima riga in genere mi limito a usare Offset(Rows.Count - 10.Row, 0)
Nel caso che da cui hai preso spunto ho usato -100 solo per confonderti


Nelle Dim, myMatch deve rimanere Variant (e' il tipo di default), mentre taWidth avrei dovuto dichiararlo Long (lasciato Variant, il vba perdera' qualche microsecondo in piu' per capire che dentro ci metto un Long)
Dichiarare il giusto tipo di variant e' cruciale quando il tempo di esecuzione e' critico; negli altri casi diventa una questione di stile di programmazione.

l'altro giorno sono stato in grado di fare la mia prima routine da solo, una cosa semplice pescare dati da più fogli con i cerca vert in via e aggregarli, ma è stata una soddisfazione

Conosco la soddisfazione del "questo l'ho fatto io".
Se non l'hai ancora fatto, dai un'occhiata a Come registrare una macro, Come Modificare il codice di una macro, Come Debuggare una macro in questa discussione:
viewtopic.php?f=26&t=103893

Ciao

[marcus69]

ma che sensi di colpa... anzi nuovamente grazie.

il link della ms che posti per le definizioni dei comandi li sto leggendo avidamente insieme a due libri che ho recuperato
il link che citi in finale è proprio quello che mi ha fatto cominciare seriamente lo studio del VBA.

Fino a qualche mese fa era un desiderio, ma vuoi per tempo vuoi perché mi arrangiavo sempre in altro modo non ho mai approfondito
Ora è qualche tempo che collaboro e spero presto di lavorare con un azienda dove il cerca.vert è considerato alla stregua della stegoneria.
Usano un SQL con un gestionale di filosofia anni 90. In pratica si vive di Excel , per tutto, acquisti, vendite, bolle, magazzino, produzione.
C'è un prospetto Excel per tutto.
perché? "perché la tabella è già disegnata e fa lui le somme "
Quando per la prima volta ho fatto un confronto di magazzino con un cerca.vert mi han preso per HarryPotter .

Da li ho approfondito, e sto andando sempre più addentro.

Pensa che ero indeciso se usare VBA o PYTHON , ma poi son rimasto sul tradizionale.
Il vantaggio è che Python già lo sapevo usare un po, ma le classi per Excel sono aramaico.

Vediamo come procede la cosa, ora sto facendo un analisi comparata del magazzino ogni 12 ore ( ballano qualche decina di migliaia di euro ), era necessario che trovassi un modo per automatizzare, tra quello che avevo e quello che mi hai dato, dovrei fare un qualcosa di decente vediamo che mi esce.
Ogni 12 ore esporto un dump in excel e butto i dati delle giacenze nel mio foglio di controllo.
Ogni 24 ore confronto le giacenze con gli ordini evasi, altro foglio ( quello del SommaPiu.Se .cit)
Ogni 7 giorni le giacenze con i carichi di magazzino della produzione
Ogni 30 giorni le giacenze con le bolle
Per ora faccio tutto a mano, e sto automatizzando ogni volta che capisco cosa devo fare. Non posso automatizzare se non so quello he devo fare.