excel vba ERRORE di Run Time '5' Chiamata di Routine o Argomento non Validi

Question

Buon giorno a tutti

Utilizzo una Macro che tramite UserForm , mi permette di confrontare i dati presenti nella colonna A con quelli presenti nella colonna B e estrarre nella colonna C quelli che non sono presenti nella colonna B .

Esempio   E1   1111      F1   1111   

                 E2   2222      F2   2222

                 E3   3333      F3   3333

                 E4   4444      F4   5555

Tramite  CommandButton  , nella colonna  G , nella Cella G1  avro'   4444 ( ...nel caso di piu' dati non presenti in colonna E i dati saranno ordinati da G1,G2,G3 eccetera ... )

Questa routine viene poi ripetuta per eseguire le stesse azioni nelle Colonne  G , H , I

Le Routine sono 2    Private Sub cbCercaCodEliminati_Click()   e 

Private Sub cbCercaCodNuovi_Click()

Il range di dati e' di 4'000 righe  

l'errore in oggetto avviene solo per la routine  **Private Sub cbCercaCodEliminati_Click()**mentre l'altra esegue tutto il ciclo alla perfezione .

La cosa strana e' che i codici delle due routine , sono identici , cambiano solo i riferimenti alle colonne .

Questo il codice

Private Sub cbCercaCodEliminati_Click()

Dim rngA As Range

  Dim rngB As Range

  Dim arA As Variant

  Dim arB As Variant

  Dim sA As String

  Dim vEle As Variant

  Dim nStart As Single

  Dim nStop As Single

 nStart = Timer

  Set rngA = Worksheets("Trova").Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))

  Set rngB = Worksheets("Trova").Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))

  arB = Application.Transpose(rngB.Value)

  sA = "#" & Join(Application.Transpose(rngA.Value), "#") & "#"

  For Each vEle In arB

    sA = Replace(sA, "#" & vEle & "#", "#")

  Next

  sA = Mid(sA, 2, Len(sA) - 2)                                                                   ' il Debug evidenzia in Giallo questa riga

  arA = Split(sA, "#")

  Set rngA = Worksheets("Trova").Range(Cells(2, 6), Cells(UBound(arA) + 2, 6))

  rngA.Value = Application.Transpose(arA)

  Set rngA = Nothing

  Set rngB = Nothing

  nStop = Timer

  Debug.Print nStop - nStart

  On Error GoTo XIT

    Application.ScreenUpdating = False

XIT:

        Application.ScreenUpdating = True

End Sub

Private Sub cbCercaCodNuovi_Click()

Dim rngA As Range

  Dim rngB As Range

  Dim arA As Variant

  Dim arB As Variant

  Dim sA As String

  Dim vEle As Variant

  Dim nStart As Single

  Dim nStop As Single

 nStart = Timer

  Set rngA = Worksheets("Trova").Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))

  Set rngB = Worksheets("Trova").Range(Cells(2, 8), Cells(Rows.Count, 8).End(xlUp))

  arB = Application.Transpose(rngB.Value)

  sA = "#" & Join(Application.Transpose(rngA.Value), "#") & "#"

  For Each vEle In arB

    sA = Replace(sA, "#" & vEle & "#", "#")

  Next

  sA = Mid(sA, 2, Len(sA) - 2)

  arA = Split(sA, "#")

  Set rngA = Worksheets("Trova").Range(Cells(2, 9), Cells(UBound(arA) + 2, 9))

  rngA.Value = Application.Transpose(arA)

  Set rngA = Nothing

  Set rngB = Nothing

  nStop = Timer

  Debug.Print nStop - nStart

  On Error GoTo XIT

    Application.ScreenUpdating = False

XIT:

        Application.ScreenUpdating = True

End Sub

Dove sbaglio ??? grazie per qualsiasi suggerimento      Claudio P

Answer 1

Ciao Claudio,

un po' per esercizio ho voluto provare ad utilizzare una Tabella (oggetto ListObject in VBA) per elaborare i "listini".

Il vantaggio delle "Tabelle" è la facile individuazione degli intervalli grazie al fatto che possono essere intercettati gli intervalli ad es. in base alle intestazioni di colonna o in base al nome assegnato alla tabella.

Come dicevo ritengo che sia possibile utilizzare solo due colonne per i dati dei listini (vecchi e nuovi) e poi eventualmente varie colonne di report (ho visto che tramite una delle userform sposti i listini vecchi o quelli nuovi per ricercare i "doppi".

Se vuoi dai un'occhiata a questo file (dove però ho eliminato le userform e dove è presente il solo codice per gestire i report restituiti (Codici Vecchi Doppi, Codici Nuovi Doppi, Codici Eliminati e Codici Nuovi).

Il tutto a titolo di esempio :)

File esempio con tabella

Con il pulsante Elabora Tutto vengono eseguiti tutti i confronti.

Con il pulsante Azzera Risultati vengono cancellati i risultati dei confronti.

Il codice VBA è presente tutto nel Modulo1.

Answer 2

Allora

Cerca Codici Nuovi confronta una serie di numeri presenti in nella Colonna G  (Listino  Nuovo) con la serie di numeri nella Colonna H  (Listino Vecchio)

Nella Colonna I  vengono riportati tutti i numeri che sono in A e non sono presenti in B (quindi codici nuovi )

La stessa cosa per Cerca Codici Eliminati , cerca  nella Colonna E (Listino Vecchio) e confronta con Colonna F (Listino Nuovo)

Nella Colonna G vengono riportati  tutti i numeri che sono presenti in E e non sono presenti in F

In poche parole vengono confrontate due serie di codici non identiche e bisogna estrarre quelli che non erano nel Listino Vecchio (Nuovi) e quelli che non sono nel Listino Nuovo (Eliminati dal Vecchio )

Grazie   Claudio P

Perdonami come mai ci sono tre colonne con intestazione "Listino Nuovo" (colonne A, E, G) e due colonne "Listino Vecchio" (colonne D, H)?

Sono valori diversi?

Dici che confronti colonna G con colonna H ma poi in colonna I vuoi i dati presenti in A (che non sono dati soggetti a controllo) ma non presenti in B (che sono sempre dei dati non presenti nel precedente confronto).

Quindi dopo il confrontro tra G e H occorre anche un ulteriore controllo su A e B?

E nelle due colonne I e G come vanno inseriti i dati?

In maniera consecutiva o in corrispodenza di qualche particolare valore?

Answer 3

Nella finestra immediata vedi il valore solo quando è lanciata la procedura.

Se dici che Len(sA) = 1 allora viene dato errore perché nella funzione Mid viene passato un argomento "negativo" quando deve essere maggiore di zero.

Answer 4

Buon Giorno e grazie per la disponibilta'

Ho inserito la riga   Debug.Print Len(sA)  poi Ctrl+G   IMMEDIATA   ....nessun valore ...

Ho eseguito  di nuovo la Macro  cbCercaCodEliminati_Click(), Errore , Debug

sono andato a vedere i valori che appaiono se si passa il cursore  nella riga evidenziata in giallo :

sA = "#"

Len(sA) = 1

****Cells(UBound(arA) + 2, 6))      Tipo Non Corrispondente

?????

Perche' , lo stesso codice Identico ,   cbCercaCodNuovi_Click() non da' lo stesso errore ?????

Le differenze sono solo nelle righe dove sono indicate le colonne da scansionare ..

cbCercaCodEliminati_Click()

Set rngA = Worksheets("Trova").Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp))

Set rngB = Worksheets("Trova").Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))

Set rngA = Worksheets("Trova").Range(Cells(2, 6), Cells(UBound(arA) + 2, 6))

**cbCercaCodNuovi_Click()**Set rngA = Worksheets("Trova").Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))

Set rngB = Worksheets("Trova").Range(Cells(2, 8), Cells(Rows.Count, 8).End(xlUp))

Set rngA = Worksheets("Trova").Range(Cells(2, 9), Cells(UBound(arA) + 2, 9))

Ho provato a cambiare  le colonne da scansionare , senza nessun risultato ....

Misteri della fede .....  come posso risolvere ???  Grazie

                              Grazie    Claudio P

Answer 5

Mi viene da pensare che Len(sA) possa essere uguale o minore di 2 e quindi la funzione Mid dia errore**.**

Prova a inserire prima di quella riga Debug.Print Len(sA) e vedere nella finestra immediata (Ctrl+G nell'editor VBA) che valore viene restituito**.** ****