EXCEL: SOSTITUZIONE AUTOMATICA DATI INSERITI

[commerciale]

Buongiorno,
Ho un excel "raccolta ordini" che i venditori utilizzano per l’invio di ordini.
Nella colonna A mi devono inserire semplicemente il codice articolo composto da 9 caratteri (esempio ABB00010A ) ed in automatico con una serie di “cerca vert” mi carica tutti i dati di prezzo quantità ecc..

Ho ricevuto la richiesta (ma solo da parte di alcuni utilizzatori) di poter inserire al posto del codice articolo il barcode (composto da 13 caratteri esempio 8000123456789).
Il problema è che posso avere chi lo vuole fare l'inserimento dei dati tramite il codice articolo, chi tramite barcode e addirittura chi compila un po’ con l’uno e un po’ con l’altro metodo.

Tenendo conto che:
1 - Il file deve essere quindi uguale per tutti a prescindere da come lo utilizzano
2 - Nella colonna A ho bisogno di visualizzare sempre il codice articolo sia che inseriscano i dati tramite codice articolo sia che lo inseriscano tramite barcode
3 - Non è mia intenzione creare nuove colonne di "supporto" anche se eventualmente nascoste

La mia richiesta quindi è:
1 - Esiste una funzione automatica di excel che mi sostituisce nelle celle della colonna A l’eventuale barcode in codice articolo (prendendo il riferimento naturalmente in una apposita tabella)?
2 – Se non è possibile, che tipo di vba devo utilizzare?

Grazie mille per tutto l’aiuto che sarete in grado di darmi.

Saluti

[ricky53]

Ciao,
premesso che utilizzare la stessa cella per avere due informazioni diverse è contrario ad ogni logica di informatica !
Veniamo al quesito:

Confermi che i codici articolo sono tutti lungi "9" caratteri ed i codici a barre son tutti lunghi "13" caratteri?

La tabella di corrispondenza tra codice a barre e codice articolo è sempre aggiornata, ossia potrebbe verificarsi il caso di un codice a barre inesistente, in tal caso si deve gestire l'errore?


Un esempio di come iniziare a lavorare su questa doppia gestione (che mi piace poco lo ripeto)

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
        If Len(Target) <> 13 And Len(Target) <> 9 And Target <> "" Then
            MsgBox "Codice Articolo  o  Codice a Barre  formalmente errato"
            Exit Sub
        End If
        If Len(Target) = 13 Then
            Application.EnableEvents = False
' qui va inserita la ricerca del codice articolo partendo dal codice a barre
' ....
            Application.EnableEvents = True
        End If
    End If
End Sub


[ricky53]

Ciao,
continuando con la mia proposta

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
        If Len(Target) <> 13 And Len(Target) <> 9 And Target <> "" Then
            MsgBox "Codice Articolo  o  Codice a Barre  formalmente errato"
            Exit Sub
        End If
        If Len(Target) = 13 Then
            Application.EnableEvents = False
' qui va inserita la ricerca del codice articolo partendo dal codice a barre
' ....
            Codice = Application.Match(Target.Value, Worksheets("Foglio2").Columns(1), 0)
            If IsError(Codice) Then
                MsgBox "Codice a barre non presente"
                Target.Select
                GoTo Continua
            Else
                Target = Worksheets("Foglio2").Cells(Codice, "B")
            End If
Continua:
            Application.EnableEvents = True
        End If
    End If
End Sub


Si può aggiungere un messaggio di conferma della sostituzione del codice a barre con il codice articolo ... questo perchè la cella utilizzata è una sola e l'utente non sa a memoria la corrispondenza tra i due codici ...

A mio avviso occorre un'altra colonna che faccia vedere all'utente i due codici

E' una sicurezza per l'utente

[commerciale]

Ciao e grazie.
L'informazione è la stessa, è come se nella cella sia che scrivo "penna" sia che scrivo "biro" in ogni caso mi deve uscire penna (esempio stupido spero sia chiaro...).

Confermo che i codici articolo sono tutti lungi "9" caratteri ed i codici a barre son tutti lunghi "13" caratteri. In caso contrario è errata l'imissione del dato e volendo l'errore si può gestire.

Appena riesco metto in pratica i tuoi consigli, grazie mille!

[ricky53]

Ciao,
si ma quando si lavora con i codici la cosa è più complessa comunque ...

Prova e ci trovi QUI !!!

[commerciale]

Provato e funziona tutto perfettamente! Fantastico!! Grazie mille Ricky! Ora mi manca solo il test con il lettore dei codici a barre che farò stasera.
Il messaggio di conferma sostituzione del codice a barre con il codice ean non serve, è già prevista una colonna con la descrizione completa dell'articolo di fianco.
Mi è piaciuto moltissimo anche il messaggio "codice a barre non presente", come dovrei fare per far uscire il messaggio di errore anche nel caso in cui il codice articolo, anche se di 9 caratteri, non è presente nella lista??

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A11:A208")) Is Nothing Then
If Len(Target) <> 13 And Len(Target) <> 9 And Target <> "" Then
MsgBox "Codice Articolo o Codice a Barre formalmente errato"
Exit Sub
End If
If Len(Target) = 13 Then
Application.EnableEvents = False
                        'ricerca corrispondenza ean/listino
Codice = Application.Match(Target.Value, Worksheets("LISTINO").Columns(7), 0)
If IsError(Codice) Then
MsgBox "Codice a barre non presente"
Target.Select
GoTo Continua
Else
Target = Worksheets("LISTINO").Cells(Codice, "A")
End If
Continua:
Application.EnableEvents = True
End If
End If
End Sub


Se serve come chiarimento posso girarti il file per e-mail (non posso collegarmi a siti di sharing...)
Saluti

[ricky53]

Ciao,
prova con questa versione del codice

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then
        MsgBox "Selezionare una sola cella", vbExclamation
        Exit Sub
    End If
    If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
        If Len(Target) <> 13 And Len(Target) <> 9 And Target <> "" Then
            MsgBox "Codice Articolo  o  Codice a Barre  formalmente errato", vbExclamation
            Exit Sub
        End If
       
        Application.EnableEvents = False
        If Len(Target) = 13 Then
            Codice = Application.Match(Target.Value, Worksheets("Tabella").Columns(1), 0)
            If IsError(Codice) Then
                MsgBox "Codice a barre non presente", vbExclamation
                Target.Select
                GoTo Fine
            Else
                Target = Worksheets("Tabella").Cells(Codice, "B")
            End If
        Else
            Codice = Application.Match(Target.Value, Worksheets("Tabella").Columns(2), 0)
            If IsError(Codice) Then
                MsgBox "Codice Articolo non presente", vbExclamation
                Target.Select
                GoTo Fine
            End If
        End If

Fine:
        Application.EnableEvents = True
    End If
End Sub


Non avendo un file sul quale fare dei riscontri, ho lasciato le colonne che avevo utilizzato all'inizio.


CONSIGLIO: NON togliere l'indentazione che ho inserito nel codice perchè con l'indentazione si aumenta la leggibilità del codice (per esempio si capisce subito dove inizia/prosegue e dove finisce un "If ... Else ... End If"

[commerciale]

Sembra funzionare a meraviglia!!
L'identazione me l'ha cancellata nel copiarlo in word e non me n'ero accorto...

Una informazione:

If Target.Count > 1 Then
MsgBox "Selezionare una sola cella", vbExclamation
Exit Sub
End If

Mi spieghi meglio a cosa serve?

Grazie ancora

Stefano

[ricky53]

Ciao,
è un controllo sul numero di celle selezionate se ">1" compare il messaggio e non fa nulla altrimenti si avrebbe errore ...

Prova a commentare l'istruzione e seleziona due o più celle per cancellare qualcosa

[commerciale]

ok, avevo visto che usciva il messaggio, intendevo tecnicamente a cosa serve, volendo si può togliere o sbaglio?

[ricky53]

Ciao,
tecnicamente lo avevo scritto: evita gli errori.

Se proprio lo vuoi togliere ... io ti consiglio di togliere solo il messaggio e lasciare il controllo con "Exit Sub", proprio per evitare errori ...
Hai provato?

[commerciale]

Si ho provato!
E ho anche capito a cosa serve: se inserisco più codici articolo in una volta tutto ok ma se inserisco più codici ean alla volta la VBA andrebbe in tilt... giusto?

[ricky53]

Ciao,
cosa intendi con

"... se inserisco più codici" ean alla volta

???
Quali passi fai?


NO. Avresti errori solo se selezioni più celle, scrivi qualcosa e premi invio
oppure
fai cancellazioni su più celle

[ricky53]

Ciao,
leggi il mio precedente messaggi e poi ...

prova questa nuova versione:

Codice: Seleziona tutto

Private Sub Worksheet_Change(ByVal Target As Range)
' TOLTE
'    If Target.Count > 1 Then
'        MsgBox "Selezionare una sola cella", vbExclamation
'        Exit Sub
'    End If

    If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
       
        On Error GoTo Fine ' <<======== AGGIUNTA
       
        If Len(Target) <> 13 And Len(Target) <> 9 And Target <> "" Then
            MsgBox "Codice Articolo  o  Codice a Barre  formalmente errato", vbExclamation
            Exit Sub
        End If
       
        Application.EnableEvents = False
        If Len(Target) = 13 Then
            Codice = Application.Match(Target.Value, Worksheets("Tabella").Columns(1), 0)
            If IsError(Codice) Then
                MsgBox "Codice a barre non presente", vbExclamation
                Target.Select
                GoTo Fine
            Else
                Target = Worksheets("Tabella").Cells(Codice, "B")
            End If
        Else
            Codice = Application.Match(Target.Value, Worksheets("Tabella").Columns(2), 0)
            If IsError(Codice) Then
                MsgBox "Codice Articolo non presente", vbExclamation
                Target.Select
                GoTo Fine
            End If
        End If

Fine:
        Application.EnableEvents = True
    End If
End Sub


[commerciale]

Buongiorno e grazie ancora per il tuo aiuto,
Con "se inserisco più codici ean alla volta" intendo il copiare da una tabella 5 codici ean e buttarceli dentro direttamente.
Preferisco la precedente versione che da un messaggio di errore mentre l'ultima proposta non da messaggi e chi lo utilizza potrebbe non capire il perchè non traduce gli ean in codice articolo.

Mi sono girato il file con "we transfer", naturalmente modificando alcuni parti personali, logo ecc... se vuoi provarlo e darmi consigli su come migliorarlo ulteriormente mi faresti un gran favore!

Codice: Seleziona tutto

File (1 MB totali)
FILE_ORD_AG_LSC FEBBRAIO 2014 3.01. SENZA DATI PERS.xls
Link di download
http://we.tl/tO9Pw4DmRw


[ricky53]

Ciao,
l'inserimento di più codici articolo è fattibile ma NON si può fare l'inserimento di più codici a barre.


Il foglio ordini è protetto quindi non posso vedere le formule che hai utilizzato.
L'impostazione generale va bene.

A cosa serve il foglio "Stock" ?

[commerciale]

Ciao, la pass te l'ho girata in privato.
Nell’azienda dove lavoro per “Stock” intendiamo alcuni articoli che oltre ad avere un prezzo normale con la relativa scontistica possono essere venduti, ad alcuni clienti, ad un prezzo netto senza sconti (indicanto nella colonna SPC "Stock").

[ricky53]

Ciao,
provo e ci risentiamo

[ricky53]

Ciao,
ho fatto un po' di prove, ho visto la struttura e le formule ... Non ho nessuna segnalazione da fare. E' un buon file.

Buon lavoro e vai avanti così.

[commerciale]

Grazie mille!
Una richiesta velocissima:
Quando trasforma il codice ean in codice articolo è possibile far posizionare il cursore automaticamente alla cella a destra libera (nella colonna D) anzichè in quella sotto?