MACRO CERCA VALORE IN UN FILE E COPIA 4 celle adiacenti

[warpPartecipante warp]

gibra ha scritto:

No, stai sbagliando tutto e ti stai complicando la vita inutilmente.  

replicando 3 volte "With ThisWorkbook.Worksheets("Foglio1")/2/3 (che si riferisce ai fogli del filemaster.xls) alla fine raggiungo il mio scopo, che è quello di cercare i codici contenuti nei 3 fogli del file "filemaster.xls" nei file "foglicassa" dove i dati si trovano solo nel "Foglio1" e copiare le 4 celle adiacenti da questi ultimi al filemaster.xls nei rispettivi (Foglio1, Foglio2 e Foglio3) ... 

perchè ho sbagliato tutto? .. quello che mi manca è creare un ciclo per il "with" al posto di replicarlo 3 volte... 

[warpPartecipante warp]

qualcuno può darmi indicazioni su come creare un ciclo per evitare di replicare 3 o più volte il codice con il “with”? Quindi per selezionare man mano i fogli contenuti nel “filemaster.xls... 

[vecchio fracSenior Moderator vecchio frac Karma: 249 pts]

warp ha scritto:

qualcuno può darmi indicazioni su come creare un ciclo

Non voglio fare dei danni a intromettermi in una discussione in cui gibra sta dando il suo contributo, ma in linea generale il suggerimento è che devi impostare un ciclo For Each ... Next che scorra i fogli della cartella attiva e che ogni volta passi il riferimento al foglio corrente al costrutto With. Lo scheletro base di questo ciclo è fatto così:

Set wsData = wbData.Worksheets("Foglio1")

For Each ws in ThisWorkbook.Worksheets
    With ws
    ...
    End With
Next

Al netto dei controlli da fare sui fogli.

I codici VBA non sono così difficili da leggere, ma bisogna familiarizzare con il modello a oggetti degli applicativi interessati, e prendere confidenza ocn un po' di terminologia.

[warpPartecipante warp]

vecchio frac ha scritto:

Non voglio fare dei danni a intromettermi in una discussione in cui gibra sta dando il suo contributo, ma in linea generale il suggerimento è che devi impostare un ciclo For Each ... Next che scorra i fogli della cartella attiva e che ogni volta passi il riferimento al foglio corrente al costrutto With. Lo scheletro base di questo ciclo è fatto così:

Grazie vecchio frac, in serata provo il tuo consiglio.. spero di portarlo a bordo ... 
Gibra mi ha risolto un grandissimo problema, xkè nel caso in cui non riesco a fare questo ciclo, userò la sua soluzione mettendo i singoli fogli del file "filemaster.xls" in file separati ... oppure lasciare tanti "with" per quanti sono i fogli .. 
Grazie ancora per il suggerimento. 

[warpPartecipante warp]

vecchio frac ha scritto:

Set wsData = wbData.Worksheets("Foglio1")For Each ws in ThisWorkbook.Worksheets    With ws    ...    End WithNext

Al netto dei controlli da fare sui fogli.

I codici VBA non sono così difficili da leggere, ma bisogna familiarizzare con il modello a oggetti degli applicativi interessati, e prendere confidenza ocn un po' di terminologia.

Vecchio frac ... FUNZIONA!!! l'ho provata ora .. e funziona alla grande!! Grazie mille!!
ti posto l'intero codice. 

Sub RiportaDatiCliente()
    Dim wbData As Workbook
    Dim wsData As Worksheet
    Dim rCel As Range
    Dim sFolderName As String, sBookName As String
    Dim lColonnaRicerca As Long, lLastRow As Long
    Dim bOpened As Boolean
    Dim vRow As Variant, vCode As Variant
    Dim i As Long

    '--- parametri ricerca da modifcare
    sFolderName = ThisWorkbook.Path & "\controllo\"
    '///sBookName = "fogliocassaXYZ.xls"    '//"SUB 123_19.xls"
    lColonnaRicerca = 3   ' corretto (era 2)
    '----------------------------------------------

    Application.ScreenUpdating = False
    '--- referenzia il file utilizzato per la ricerca, può essere aperto o chiuso
    
    
    Rem APRE OGNI FILE NELLA CARTELLA, LEGGE I DATI E LO CHIUDE
    sBookName = Dir$(sFolderName & "*.xls")
    Do While sBookName <> ""
        bOpened = False

        ' intercetto l'errore se il file non è già aperto, lo apro per leggerlo
        On Error Resume Next
        Set wbData = Workbooks(sBookName)
        If wbData Is Nothing Then
            Set wbData = Workbooks.Open(sFolderName & sBookName, , True)
            bOpened = True
            Err.Clear
        End If
        On Error GoTo 0
        'On Error GoTo Uffa
        ' ora il file è aperto, procedo alla lettura dei dati
    
        '--- modifica il nome del foglio nel quale effettuare la ricerca
        

Set wsData = wbData.Worksheets("Foglio1")

       
       

For Each ws In ThisWorkbook.Worksheets
    With ws
     '--- i codici si trovano in colonna B
            lLastRow = .Cells(Rows.Count, "b").End(xlUp).Row
            For i = 1 To lLastRow
                vCode = .Cells(i, 2)
                'If vCode = 23797 Then Stop
                If Len(vCode) Then
                    vRow = Application.Match(vCode, wsData.Columns(lColonnaRicerca), 0)
                    If Not IsError(vRow) Then
                        '--- copia 4 celle a partire dalla colonna J e le incolla a partire dalla colonna ay
                        .Cells(i, "a").Resize(, 4).Value = wsData.Cells(vRow, "b").Resize(, 4).Value
                    Else
                        '.Cells(rCel.Row, "a").Resize(, 4).Value = Array("not found", "not found", "not found", "not found")
                    End If
                End If
            Next i
    End With
Next
        
      
        
        
        
        wbData.Close False  ' chiudo il file
        Set wbData = Nothing
        sBookName = Dir$()  ' leggo il prossimo
    Loop
    
ExitHere:
    Application.ScreenUpdating = True
    On Error Resume Next
    If bOpened Then wbData.Close False

    Set rCel = Nothing: Set wsData = Nothing: Set wbData = Nothing
    Exit Sub

Uffa:
    Call MsgBox("Ohibò, si è verificato il seguente errore: " & vbNewLine & _
                CStr(Err.Number) & ": " & Err.Description & vbNewLine & vbNewLine & _
                "Codice in elaborazione: " & vCode, _
                vbCritical + vbOKOnly, "Error message")
    Resume ExitHere
End Sub

[vecchio fracSenior Moderator vecchio frac Karma: 249 pts]

warp ha scritto:

FUNZIONA

In linea generale, quando hai del codice che si ripete, conviene studiare una soluzione che ne preveda il riuso, in questo caso con un semplice ciclo For next, in altri casi creando una subroutine o una funzione apposite, separate, magari parametriche, da richiamare al bisogno. Ciò ottimizza il tempo da spendere per il debug e facilita l'individuazione e correzione degli errori. Mi raccomando sempre Option Explicit in testa ai vostri moduli di codice, e dichiarare le variabili correttamente.

[warpPartecipante warp]

Vecchio Frac farò tesoro di quello che hai scritto!! Grazie mille!! Mitico! 

E grazie ancora a Gibra... con questo post mi son avvicinato al vba ed i vostri suggerimenti mi hanno spinto ad entrare in confidenza con il codice 💪🏻💪🏻

[Autore]

Articoli