Gestionale magazzino con più di mille articoli

[primal80]

Buongiorno
ho trovato il Vs sito cercando su internet una soluzione al mio problema lavorativo.
Attualmente mi occupo della gestione del magazzino di una società che lavora con più reparti (unico magazzino ma diversi reparti es. pasticceria, gelateria, ristorante ecc..) .
Il lavoro dovrebbe permettere di conoscere i consumi effettivi di ciascun reparto e determinare così l'eventuale reparto da migliorare o chiudere.
Il gestionale che usano non ci permette di conoscere i costi di ciascun reparto ma solo quelli complessivi
Su internet ho trovato un file excel adatto al mio caso ma dopo un pò il file è diventato molto lento (cerca.vert deve trovare un dato valore tra più di mille articoli) e la stessa macro utilizzata per l'inserimento dati impiega troppo tempo rispetto al primo utilizzo
Al momento Vi chiedo solo se fosse possibile:
Velocizzare la macro INSERIMENTO DATI
Velocizzare il cerca.vert che mi PERMETTE DI TROVARE la giacenza e il costo ultimo del prodotto (Foglio DATI)
Spero di esser stata chiara
Inoltre con le mie insufficienti conoscenze in fatto di formule, ho provato a creare un menu a tendina che mi permetta di trovare l'articolo (ci sono più di mille articoli) digitando solo le prime lettere ma a volte il testo reso è totalmente diverso da quello cercato

https://file.pizza/shallots-sausage-pepperoni-limburger

Grazie a chiunque vorrà aiutarmi

[alfrimpa]

Il link al file non funziona!

[zsadist]

Il link al file non funziona!

confermo

[Anthony47]

Per le istruzioni su come allegare un file:
viewtopic.php?f=26&t=103893&p=605487#p605487

[primal80]

Buongiorno
grazie per l'interesse
Ecco un nuovo link

https://gofile.io/?c=euzfcazjcl

[Anthony47]

Il tuo file contiene circa 30mila volte la funzione OGGI; questa e' "volatile", cioe' si ricalcola in continuazione, e con ciò si porta dietro il ricalcolo di tutte le celle "subordinate"; un lavoro immane.
D'altra parte un Cerca.Vert su 1000-10mila righe sarebbe un'operazione assolutamente normale.

Facciamo cosi':
-in File /Opzioni /Formule imposta transitoriamente il ricalcolo della cartella di lavoro = Manuale
-usiamo la cella Dati!A1 per inserirvi la data odierna; metti per ora la data manualmente, da tastiera
-col comado Trova, cerca "oggi()", senza virgolette; usa le opzioni In = Cartella di lavoro e Cerca in = Formule; non eseguire ancora il comando
-sul tab Sostituisci inserisci "Dati!A1", senza virgolette
-premi Sostituisci tutto e aspetta qualche secondo fino al messaggio di completamento
-in File /Opzioni /Formule riporta il ricalcolo della cartella di lavoro = Automatico

A questo punto tutti i ricalcoli dovrebbero essere grandemente velocizzati

Per popolare la cella Dati!A1 con la data, possiamo aggiungere questo codice all'interno del modulo "QuestaCartellaDiLavoro /ThisWorkbook" del vba:

Codice: Seleziona tutto

Private Sub Workbook_Open()
Sheets("Dati").Range("A1") = Int(Now)
End Sub


Per identificare la posizione del modulo vba, vedi viewtopic.php?f=26&t=103893&p=647675#p647675

In questo modo la data viene compilata automaticamente all'apertura del file (se invece tieni il file aperto da un giorno all'altro allora troveremo un altro "evento" con cui aggiornare la data)

Vedo che la tua "Tabella_articoli" (la matrice usata nel Cerca.Vert) e' il ordine crescente: questo potrebbe farci usare un'altra formulazione del Cerca.Vert, con una formula pero' leggermente piu' complicata. Ma con le righe in gioco secondo me il gioco non vale la candela.
Inoltre le versioni piu' recenti (da un anno a questa parte) hanno ridotto il divario di prestazioni tra elenchi ordinati e disordinati

Le modifiche cosi' fatte secondo me avranno gia' impatto sull'esecuzione di INSERIMENTO_DATI; per ulteriore precauzione possiamo aggiungere questi blocchi di informazioni in queste due posizioni:

Codice: Seleziona tutto

'.. codice esistente
'..
    MsgBox ("     Dati Incompleti")
    End
    End If
'PRIMO BLOCCO DA AGGIUNGERE >>>
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'<<< FINE BLOCCO
    Range("Riga_dati").Select
    Selection.Copy
'..
'..
'..
'..
    Range("A1").Select
    'Range("Data").Select

'SECONDO BLOCCO DA AGGIUNGERE >>>     
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.Calculate
'<<< FINE BLOCCO     

End Sub


Ci sarebbero ulteriori miglioramenti che potrebbero essere apportati, principalmente evitare gli spostamenti tra i fogli, ma richiede una migliore conoscenza di cosa dovrebbe fare la macro quindi mi lascio questa opzione nel caso che i miglioramenti (con quanto fin qui suggerito) non siano adeguati.

Ciao

[primal80]

Mille grazie!
E' diventato velocissimo ed ora riesco ad inserire i dati senza spreco di tempo
Resta il problema della tendina nella casella ARTICOLO.
Attualmente inserisco le iniziali del prodotto che mi occorre in C12 (foglio Archivio Movimentazioni) e nella casella C11 scorro fra le possibili soluzioni
Io vorrei invece digitare le prime lettere direttamente nella cella C11 e poi scorrendo la tendina inserire l'articolo cercato
Ho visto che si può fare ma io non ci riesco.
Inoltre cosa insolita a volte se cerco ad esempio "scat" mi esce tra i vari risultati "Vodk" (Iniziali che non corrispondono a quelle da me cercate).
Ho già provveduto ad ordinare alfabeticamente i prodotti.

Consigli?

[Anthony47]

Lavorando sulle celle (parlo di C11 e C12) e' difficile ottenere un funzionamento dinamico, meglio si puo' fare con altri "strumenti".

Facciamo cosi':
1) Sul foglio "Archivio Movimentazioni" aggiungi una "Casella di testo" prelevandola dai Controlli ActiveX; non preoccuparti delle dimensioni; visualizza le sue Proprietà e cambia il "(Name)" da quello di default in "TBFiltro" (senza virgolette)

2) Sullo stesso foglio inserisci anche una "Casella di riepilogo", sempre prelevandola dai Controlli ActiveX; non impostare nessuna proprietà ma cambiane il "(Name)" in "LBFiltro" (senza virgolette)

3) Adesso vai sul modulo vba del foglio "Archivio Movimentazioni": tasto dx sul tab col nome foglio; scegli Visualizza Codice. E' gia' presente del codice, tu sai a che serve e io non lo tocco.

IN TESTA AL MODULO aggiungi questo codice:

Codice: Seleziona tutto

Dim wArr, ELock As Boolean       'Rigorosamente IN TESTA al modulo

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$11" Then
'Fill WArr:
    With Sheets("Descrizione articoli")
        wArr = .Range(.Range("B12"), .Range("B10000").End(xlUp)).Value
    End With
    With Me.TBFiltro
        .Text = " "
        .Text = ""
        .Visible = True
        .Top = Target.Top
        .Left = Target.Offset(0, 1).Left
        .Width = Target.Width
        .Height = Target.Height
        .BackColor = RGB(255, 255, 0)
    End With
    With Me.LBFiltro
        .Visible = True
        .Top = Me.TBFiltro.Top + Me.TBFiltro.Height + 2
        .Left = Me.TBFiltro.Left
        .Width = Me.TBFiltro.Width * 1.5
        .Height = Target.Height * 3 + 50
        .BackColor = RGB(255, 255, 200)
    End With
    Me.TBFiltro.Activate
Else
    Me.TBFiltro.Visible = False
    Me.LBFiltro.Visible = False
End If
End Sub

Private Sub lbfiltro_Click()
'
If ELock = False Then
    Debug.Print LBFiltro.ListIndex, LBFiltro.Value
    Range("C11") = Me.LBFiltro.Value
    Range("C12").Select
Else
    Debug.Print "LOCKED", LBFiltro.ListIndex, LBFiltro.Value
End If
End Sub

Private Sub TBFiltro_Change()
Dim lArr(), I As Long, J As Long, TBTxt As String, LBH As Long
'
ELock = True
'If IsEmpty(wArr) Then
'    With Sheets("Descrizione articoli")
'        wArr = .Range(.Range("B12"), .Range("B10000").End(xlUp)).Value
'    End With
'End If
ReDim lArr(1 To UBound(wArr))
TBTxt = Me.TBFiltro.Text
For I = 1 To UBound(wArr)
    If InStr(1, wArr(I, 1), TBTxt, vbTextCompare) > 0 Then
        J = J + 1
        lArr(J) = wArr(I, 1)
    End If
Next I
If J = 0 Then J = 1
ReDim Preserve lArr(1 To J)
Me.LBFiltro.List = lArr
For I = 0 To LBFiltro.ListCount - 1
    LBFiltro.Selected(I) = False
Next I
ELock = False
End Sub

Adesso vai sul foglio ArchivioMovimentazioni, cancella il contenuto di C12 e seleziona la cella C11: dovrebbe comparirti il textbox e (sotto) il listbox con tutti i valori contenuti su 'Descrizione articoli' colonna B. I controlli solo colorati in Giallo e "giallino" per renderli piu' evidenti.
Il focus e' nel textbox; se cominci a scrivere allora il contenuto del listbox sara' man mano ridotto per includere solo le voci che "contengono" la stringa che hai digitato.
Quando vuoi, puoi cliccare per selezionare una voce dal ListBox e verra' scritta in C11; TextBox e ListBox verranno ora nascosti, fino alla prossima selezione di C11

Se non vuoi effettuare una selezione ti basta selezionare una cella diversa da C11

Se il meccanismo proposto soddisfa le tue esigenze allora potrai eliminare la Convalida che hai impostato in C11; compreso il contenuto di Colonna O, se serviva solo per la Convalida.

Fai sapere...

[primal80]

Salve
grazie mille
era esattamente ciò che cercavo.
Il file grazie al Vs aiuto è decisamente migliorato
Le mie conoscenze sono davvero limitate e senza di Voi difficilmente ne sarei venuta a capo.
Purtroppo mi servirà ancora il Vs aiuto, ma voglio prima provare a fare da sola.

Grazie mille

[primal80]

Salve mi dispiace chiederVi nuovamente aiuto ma con una formula non sono riuscita a creare un metodo facile ed intuitivo che mi permetta di trovare tra i carichi effettuati, per uno stesso prodotto, tutti i relativi fornitori e i prezzi
es. se per una bob di carta ci sono più fornitori io dovrei ottenere una lista con i loro nomi e l'ultimo prezzo applicato e la data di carico
Ovviamente i prodotti dovrei sempre cercarli tra una lista come quella ottenuta nel primo foglio (con menu a tendina con ricerca parziale del testo)
Tutto questo in un nuovo foglio di lavoro che vorrei chiamare Listino prezzi

grazie per chiunque vorrà darmi una mano

[Anthony47]

Facciamo un esempio:
-supponiamo che di una Descrizione hai 10 carichi in 10 date diverse con 3 fornitori diversi e con (esagerando) 10 prezzi diversi di carico: quali informazioni vorresti ottenere?

Ciao

[primal80]

Buonasera
allora quello che mi occorre è:
ottenere dei tre fornitori l'ultimo prezzo e la relativa data (mi riferisco alla data in cui è stato registrato l'ultimo prezzo per ciascun fornitore

es Altieri bob carta € 1,75 17/02/19
Altieri bob carta € 1,95 20/03/19
Rossi bob carta € 1,19 02/01/19
Rossi bob carta € 1,40 06/04/19

dovrei ottenere inserendo "bob carta"
Altieri € 1,95 20/03/19
Rossi € 1,40 06/04/19
Spero di esser stata chiara

Grazie mille

[Anthony47]

Allora, tanto vale farlo subito con una macro, anzi con una Funzione Personalizzata.

In un Modulo standard del vba inserisci questo codice:

Codice: Seleziona tutto

Function FornitAll(ByVal myItm As String, myMov As Range) As Variant
Dim oArr(), supArr(), WArr, I As Long, myUCItm As String
Dim dLayout, myMatch, J As Long
'
ReDim oArr(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
ReDim supArr(1 To Application.Caller.Rows.Count)
For I = 1 To UBound(oArr)
    For J = 1 To UBound(oArr, 2)
        oArr(I, J) = "....."
    Next J
Next I
J = 0
dLayout = Array(2, 11, 1, 12)   '<<< Le colonne di Articolo, Fornitore, Data, Prezzo
WArr = myMov.Value
myUCItm = UCase(myItm)
For I = 1 To UBound(WArr)
    If UCase(WArr(I, dLayout(0))) = myUCItm Then
        myMatch = Application.Match(WArr(I, dLayout(1)) & "ZZ", supArr, False)
        If IsError(myMatch) Then
            J = J + 1
            If J > UBound(oArr) Then
                oArr(J - 1, 1) = ". altro ."
                oArr(J - 1, 2) = ". altro ."
                oArr(J - 1, 3) = ". altro ."
                Exit For
            End If
            supArr(J) = WArr(I, dLayout(1)) & "ZZ"
            oArr(J, 1) = WArr(I, dLayout(1))
            oArr(J, 2) = WArr(I, dLayout(3))
            oArr(J, 3) = WArr(I, dLayout(2))
        Else
            If WArr(I, dLayout(2)) > oArr(myMatch, 3) Then
                oArr(myMatch, 1) = WArr(I, dLayout(1))
                oArr(myMatch, 2) = WArr(I, dLayout(3))
                oArr(myMatch, 3) = WArr(I, dLayout(2))
            End If
        End If
    End If
Next I
FornitAll = oArr
End Function

Poi vai in un nuovo foglio dove vuoi ottenere queste informazioni; in una cella scrivi la Descrizione; supponiamo che usi A1

Poi seleziona un'area di 3 colonne per 10 righe; nella barra della formula scrivi la formula

Codice: Seleziona tutto

=FornitAll(A1;'Archivio Movimentazioni'!B14:M5500)


Conferma la formula con Contr-Maiusc-Enter, non il solo Enter

Otterrai in questo modo una tabella lunga fino a 10 fornitori con le informazioni richieste, cioe':
Fornitore /Prezzo piu' recente /Data piu' recente

Formatta le colonne di uscita come ti serve; in particolare la terza colonna va formattata come una data altrimenti risultera' "un po' criptica"

Ho scritto di inserire la formula su "10 righe", ma puoi scegliere tu il numero max di righe da popolare
Se i fornitori di quel bene sono piu' del numero di righe su cui la formula si applica allora nell'ultima riga comparira' la scritta ".altro."

Se vuoi estendere la formula su ulteriori righe:
-seleziona l'intera area attuale della formula
-estendi la selezione per ulteriori righe verso il basso
-premi Contr-Maiusc-Enter

Tutto quanto proposto e' basato sulla Funzione Personalizzata FornitAll, la cui sintassi e':

Codice: Seleziona tutto

FornitAll(DescrizioneProdotto;AreaDelleMovimentazioni)


La funzione si comporta come le funzioni standard excel; quindi ad esempio potresti inserire la DescrizioneProdotto non solo come "riferimento" ad altra cella (A1, nell'esempio), ma anche come stringa; tipo

Codice: Seleziona tutto

=FornitAll("carote";'Archivio Movimentazioni'!B14:M5500)

Questa sintassi e' data solo come esempio, suggerisco di usare una cella per l'input della Descrizione

Come fare a sfruttare un TextBox e un ListBox per avere la compilazione assistita della Descrizione penso che l'hai gia' imparato, non te lo ripeto.

Prova e fai sapere...

[primal80]

Salve
ho provato a fare come suggerito ma quando inserisco la formula
con il metodo indicato (ctrl+shift+invio) mi da un errore come da immagine

https://gofile.io/?c=6oZCb7

Non so cosa sbaglio

[Anthony47]

Il tuo file contiene una serie di "riferimenti vba" poco comuni: Office XP Web Components, Agent Control 2.0; Windows Common Controls 6.0 (SP6); Tablet PC Type Library, version 1.0.
Io non so a cosa servono; se il file non l'hai sviluppato tu allora dovrai chiedere al suo autore per conoscerne il significato e, se non disponibili sul tuo Pc, sapere dove prelevarle.

Intanto, per non avere saltuariamente l'errore che ti compare, vai sul tuo vba; tramite i comandi Menu /Strumenti /Riferimenti visualizza l'elenco delle "librerie" che sono associate al tuo file (quelle con la "spunta"); dovrebbe essercene qualcuna che, accanto al nome, presenta l'indicazione "MANCA". Intanto che ti fai spiegare dal progettista di quel file dove puoi reperire quella libreria, togli la "spunta" alla voce, e salva con Ok.
L'errore che hai indicato dovrebbe cosi' scomparire e la funzione FornitAll potrebbe cominciare a funzionare

Fai sapere...

[primal80]

salve
ho fatto quanto suggerito e ottengo dei risultati solo che
sulla prima riga del fornitore esce sempre "0"(ho provato con più articoli)
e sulla seconda riga del prezzo sempre "0"

[Anthony47]

Lavorando su file che avevi pubblicato mi dici la formula che hai usato, in quali celle l'hai inserita e il risultato che ottieni; meglio se documenti l'output con una immagine
Per le istruzioni su come creare e allegare una immagine:
viewtopic.php?f=26&t=103893&p=605488#p605488

Ciao

[primal80]

spero di aver seguito correttamente le istruzioni.

Grazie per il tempo.

[Anthony47]

Veramente avevo chiesto anche la formula che hai usato e in quali celle l'hai inserita; ma non e' importante alla luce del risultato che pubblichi.

Vai sul file che avevi pubblicato; applica il filtro automatico alla colonna Articolo, sul foglio Archivio Movimentazioni; filtra per "Carote"; poi spiega perche' l'output della formula non collima con quanto presente in archivio.

Ciao

[primal80]

Salve la formula usata è:
=FornitAll(A1;'Archivio Movimentazioni'!B14:M5500)
incollata nella barra delle formule per la tabella B1:D13
confermata con CTRL+SHIFT+INVIO

Come si può vedere oltre a non darmi il valore dell'ultimo
prezzo di carico ( RAZZANO € 0,50) mi da anche un valore "€ 1,50"
che non è associabile a nessun fornitore

saluti