Oltre a un Array di fogli aggiungere un altro foglio indipendente dall'array

Anonimo
2014-05-11T20:23:52+00:00

Ciao a tutti, ho bisogno di voi per sistemare un codice che utilizzo ormai da diverso tempo. L’ho scaricato dal sito di Mauro e riguarda un Array di fogli.Il codice è il seguente:Public Sub m()

    Dim shArray As Sheets

    Dim sh As Worksheet

On Error GoTo RigaErrore

    Set shArray = ThisWorkbook.Worksheets(Array("Foglio1", "Foglio2")

    For Each sh In shArray

        '...

        'codice da eseguire riguarda 5 TextBox. Quello che segue è un esempio di quello che viene eseguito

If TextBox1.Value > 0 Then

       .Range("A" & lRiga).Value = "=Archivio!C7"

       lRiga = lRiga + 1

Dopo questa riga quello che devo aggiungere è che il contenuto della textbox1 mi finisca nel foglio Archivio alla cella B10

A seguire se la textbox2, 3, 4 o 5 contiene un valore numerico mi riporterà il valore non solo nell’array che include 5 fogli ma anche nel foglio Archivio nella cella B11 per la textbox2, B12 texybox3 B14 textbox4 e B15 textbox15.

  End If    

Next

RigaChiusura:

    'Set a Nothing delle variabili oggetto

    Set shArray = Nothing

    Set sh = Nothing

    'uscita dalla routine

    Exit Sub

    RigaErrore:

    'mostro una MsgBox con i dati dell'errore

    MsgBox Err.Number & vbNewLine & Err.Description

    'vado alla riga di chiusura

    Resume RigaChiusura

End Sub

La mia necessità è quello di aggiungere in questo codice la possibilità di riportare su un foglio di nome archivio che non fa parte dell’array dati necessari in base a alle condizioni suindicate. Grazie

Microsoft 365 e Office | Excel | Per la casa | Windows

Domanda bloccata. Questa domanda è stata eseguita dalla community del supporto tecnico Microsoft. È possibile votare se è utile, ma non è possibile aggiungere commenti o risposte o seguire la domanda.

0 commenti Nessun commento
Risposta accettata dall'autore della domanda
Anonimo
2014-05-14T12:07:29+00:00

Ciao Geacs,

Hai fatto esattamente quello che ti ho chiesto, anzi molto di più tanto che mangiando mangiando ti viene l’appetito. Ho pensato di utilizzare il codice postato anche su un’altra cartella. 

Potrebbe essere necessario esercitare una certa attenzione, perché mentre ho cercato di rendere il codice generico, potrebbe richiedere qualche 'sintonizzazione' per affrontare uno scenario diverso. In caso di necessità, chiedi pure!

Se non ti chiedo troppo, potresti fare in modo che nella colonna A una volta copiati i collegamenti delle textbox che contengono i valori numerici del foglio archivio possa completarsi dalla cella A10 fino alla cella A25 senza andare oltre, riportando i collegamenti che trova sullo stesso foglio dalle colonne D ed E? 

Spero che io abbia capito anche se ancora lo scopo non é chiaro!

Inoltre ho notato che se dovessi inserire numeri decimali me li sposta sulla sinistra. Potresti correggere il codice in modo da farli rimanere sempre sulla destra?

Fatto!

Prova a sostituire la seconda versione del mio codice con:

'=========>>

Option Explicit

'--------->>

Private Sub CommandButton1_Click()

    Dim WB As Workbook

    Dim SH As Worksheet, aSH As Worksheet

    Dim Rng As Range, Rng2 As Range, Rng3 As Range

    Dim arrTBox As Variant

    Dim arrFogli As Variant

    Dim arrIn As Variant, arrOut() As Variant

    Dim vVal As Variant

    Dim i As Long, j As Long, k As Long, p As Long, x As Long

    Dim iRow As Long

    Const mySheets As String = "Foglio1,Foglio2,Foglio3,Foglio4,Foglio5" '<<==== Modifica

    Const sSheet As String = "Archivio"                                                      '<<==== Modifica

    Const primaRiga As Long = 10 '<<==== Modifica

    Const nonOltre As String = "A25"                                                        '<<==== Modifica

    arrFogli = Split(mySheets, ",")

    Set WB = ThisWorkbook

    Set aSH = WB.Sheets(sSheet)

    Set Rng = aSH.Range("D7")

    With Me

        arrTBox = Array(.TextBox1, .TextBox2, .TextBox3, .TextBox4, .TextBox5)

    End With

    iRow = primaRiga - 1

    For j = LBound(arrFogli) To UBound(arrFogli)

        Set SH = WB.Sheets(Trim(arrFogli(j)))

        With SH

            Set Rng2 = .Range("A" & iRow + 1, nonOltre)

            Rng2.ClearContents

        End With

    Next j

    k = Rng.Row

    For i = LBound(arrTBox) To UBound(arrTBox)

        vVal = arrTBox(i).Value

        If IsNumeric(vVal) And vVal > 0 Then

            Rng.Cells(i + 1).Value = CDbl(vVal)

            iRow = iRow + 1

            For j = LBound(arrFogli) To UBound(arrFogli)

                Set SH = WB.Sheets(Trim(arrFogli(j)))

                With SH

                    .Range("A" & iRow).Value = "=Archivio!C" & k + i

                    .Range("A" & iRow + 1).Value = "=Archivio!D" & k + i

                End With

            Next j

        Else

            iRow = iRow - 1

        End If

        iRow = iRow + 1

    Next i

    For i = LBound(arrFogli) To UBound(arrFogli)

        j = 0

        k = 0

        p = 0

        Set SH = WB.Sheets(Trim(arrFogli(i)))

        With SH

            x = .Range(nonOltre).Row - iRow

            Set Rng3 = .Range("D" & .Rows.Count).End(xlUp)

            Set Rng3 = .Range(Rng3, Rng3.End(xlUp)).Resize(x \ 2, 2)

            arrIn = Rng3.Value

            j = UBound(arrIn, 1)

            ReDim arrOut(1 To j * 2)

            For k = 1 To j

                p = p + 2

                arrOut(p - 1) = "=" & Rng3.Cells(k, 1).Address

                arrOut(p) = "=" & Rng3.Cells(k, 2).Address

            Next k

            .Range("A" & iRow + 1).Resize(j * 2).Value = Application.Transpose(arrOut)

        End With

    Next i

End Sub

'<<=========

===

Regards,

Norman

La risposta è stata utile?

0 commenti Nessun commento

20 risposte aggiuntive

Ordina per: Più utili
  1. Anonimo
    2014-05-13T16:21:54+00:00

    Ciao Norman,

    Ho pensato di pubblicare il file che potresti vedere in modo da capire dove va corretto il codice. Ho la necessità che i collegamenti del foglio archivio vengano copiati nei 5 fogli. Invece cliccando su commandButton vengono copiati solo nel foglio. il file lo trovi  qui

    Grazie per quello che fai.

    Ciao Geacs,

    Io non sono affatto sicuro che ho capito lo scopo del codice perché mi sembra strano che le stesse celle della colonna A su ciascuna delle cinque fogli  Foglio1-Foglio5 dovrebbero riferirsi allo stesso intervallo del foglio Archivio. Inoltre, siccome non è stato indicato un valore iniziale per la variabile lRiga, ogni volta che il codice viene eseguito, i valori precedenti saranno parzialmente o completamente sovrascritti. Mentre potrei capire quest'ultimo caso, i primo caso me causerebbe qualche preoccupazione - ma, ovviamente, non so il tuo obiettivo finale!

    Pertanto, ti consiglio di provare questo codice su una copia del file!  

    '=========>>

    Option Explicit

    '--------->>

    Private Sub CommandButton1_Click()

        Dim WB As Workbook

        Dim SH As Worksheet, aSH As Worksheet

        Dim Rng As Range

        Dim arrTBox As Variant

        Dim arrFogli As Variant

        Dim vVal As Variant

        Dim i As Long, j As Long, k As Long

        Dim lRiga As Long

        Const mySheets As String = "Foglio1,Foglio2,Foglio3,Foglio4,Foglio5"

        arrFogli = Split(mySheets, ",")

        Set WB = ThisWorkbook

        Set aSH = WB.Sheets("Archivio")

        Set Rng = aSH.Range("D7")

        With Me

            arrTBox = Array(.TextBox1, .TextBox2, .TextBox3, .TextBox4, .TextBox5)

        End With

        k = Rng.Row

        For i = LBound(arrTBox) To UBound(arrTBox)

            vVal = arrTBox(i).Value

            If IsNumeric(vVal) And vVal > 0 Then

                Rng.Cells(i + 1).Value = vVal

                lRiga = lRiga + 1

                For j = LBound(arrFogli) To UBound(arrFogli)

                    Set SH = WB.Sheets(Trim(arrFogli(j)))

                    With SH

                        .Range("A" & lRiga).Value = "=Archivio!C" & k + i

                        .Range("A" & lRiga + 1).Value = "=Archivio!D" & k + i

                    End With

                Next j

                lRiga = lRiga + 1

            End If

        Next i

    End Sub

    '<<=========

    Tuttavia, forse la tua intenzione sarebbe anche di sostituire i valori inseriti in precedenza dalla macro e se questo fosse il caso, prova invece la seguente versione:

    '=========>>

    Option Explicit

    '--------->>

    Private Sub CommandButton1_Click()

        Dim WB As Workbook

        Dim SH As Worksheet, aSH As Worksheet

        Dim Rng As Range

        Dim arrTBox As Variant

        Dim arrFogli As Variant

        Dim vVal As Variant

        Dim i As Long, j As Long, k As Long

        Dim iRow As Long

        Dim blNumeric As Boolean

        Const mySheets As String = "Foglio1,Foglio2,Foglio3,Foglio4,Foglio5"

        arrFogli = Split(mySheets, ",")

        Set WB = ThisWorkbook

        Set aSH = WB.Sheets("Archivio")

        Set Rng = aSH.Range("D7")

        iRow = 10                                            

        With Me

            arrTBox = Array(.TextBox1, .TextBox2, .TextBox3, .TextBox4, .TextBox5)

        End With

        Rng.Resize(UBound(arrTBox) + 1).ClearContents

        For j = LBound(arrFogli) To UBound(arrFogli)

            Set SH = WB.Sheets(Trim(arrFogli(j)))

            With SH

                SH.Range("A" & iRow + 1).Resize((UBound(arrTBox) + 1) * 2).ClearContents

            End With

        Next j

        k = Rng.Row

        For i = LBound(arrTBox) To UBound(arrTBox)

            blNumeric = False

            vVal = arrTBox(i).Value

            If IsNumeric(vVal) And vVal > 0 Then

                blNumeric = True

                Rng.Cells(i + 1).Value = vVal

                iRow = iRow + 1

                For j = LBound(arrFogli) To UBound(arrFogli)

                    Set SH = WB.Sheets(Trim(arrFogli(j)))

                    With SH

                        .Range("A" & iRow).Value = "=Archivio!C" & k + i

                        .Range("A" & iRow + 1).Value = "=Archivio!D" & k + i

                    End With

                Next j

            Else

                iRow = iRow - 1

            End If

            iRow = iRow + 1

        Next i

    End Sub

    '<<=========

    ===

    Regards,

    Norman

    La risposta è stata utile?

    0 commenti Nessun commento
  2. Anonimo
    2014-05-13T08:58:19+00:00

    Ciao Norman,

    Ho pensato di pubblicare il file che potresti vedere in modo da capire dove va corretto il codice. Ho la necessità che i collegamenti del foglio archivio vengano copiati nei 5 fogli. Invece cliccando su commandButton vengono copiati solo nel foglio. il file lo trovi  qui

    Grazie per quello che fai.

    La risposta è stata utile?

    0 commenti Nessun commento
  3. Anonimo
    2014-05-12T18:04:17+00:00

    Ciao Geacs,

    Per converso, nelle mie prove, solo valori numerici positivi immessi nelle caselle di testo determinano l'inserimento della formula in qualsiasi cella nell'intervallo C7: D11 del foglio attivo. Forse, non avevi cancellato i valori precedenti quando tu hai i ri-eseguito il codice? Per ovviare a questa possibilità. sostituisci:

    '--------->>

    Private Sub CommandButton1_Click()

        Dim lRiga As Long

        lRiga = 1

        With ActiveSheet

    con

    '--------->>

    Private Sub CommandButton1_Click()

        Dim lRiga As Long

        lRiga = 1

        With ActiveSheet

            .Range("C7:D11").ClearContents

    Approfitto per ricordarti del mio consiglio: 

    Tuttavia, verifica che questo codice inserisce la formula desiderata nelle celle di destinazione corrette nel caso in cui uno o più delle caselle di testo non hanno un valore numerico! 

    ===

    Regards,

    Norman

    La risposta è stata utile?

    0 commenti Nessun commento
  4. Anonimo
    2014-05-12T17:32:16+00:00

    Ciao Norman,

    Ho provato e riprovato ma continua a riportarmi ancora il collegamento anche di quelle celle dove nella textbox non viene inserito nessun valore. In sostanza le riporta tutte dalla textbox1 alla textbox5. Spero che riesci a trovare la soluzione. Grazie

    La risposta è stata utile?

    0 commenti Nessun commento