Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Inserire riferimenti per inputbox

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Inserire riferimenti per inputbox

Postdi BG66 » 17/12/17 09:18

Ciao Anthony,
su altro blog stò tirando matto il grande Marius (che non finirò mai di ringraziare) e per non esagerare...vengo a Canossa ;)
Tutto parte da una tua macro che mi ha aiutato oltre ogni limite (http://www.pc-facile.com/forum/viewtopic.php?f=26&t=108413) e precisamente:
Codice: Seleziona tutto
Sub FormUpdate2()
    Dim fCell As Range, aForm As String, oPart As Variant, nPart As Variant
    Dim Ws As Worksheet
    '
    oPart = Application.InputBox(Prompt:="Valore da ELIMINARE:", _
Title:="Cerca e Sostituisci nelle formule", Type:=2)
If Len(oPart) = 0 Or oPart = False Then
    MsgBox ("Stringa non valida; la macro viene terminata")
    Exit Sub
End If
nPart = Application.InputBox(Prompt:="Valore da Inserire:", _
  Title:="Cerca e Sostituisci nelle formule", Type:=2)
If Len(oPart) = 0 Or oPart = False Then
    MsgBox ("Stringa non valida; la macro viene terminata")
    Exit Sub
End If
    '
    Dim Rng As Range
    Set Rng = Application.InputBox(Prompt:="Selezione intervallo:", _
    Title:="Intervallo da modificare", Type:=8)

    Application.DisplayAlerts = False
    Application.EnableEvents = False
           If IsNull(Rng.HasFormula) Or Rng.HasFormula Then   'MMM
            For Each fCell In Rng
                aForm = fCell.Formula
                If InStr(1, aForm, oPart, vbTextCompare) > 0 Then
                     aForm = Replace(aForm, oPart, nPart, , , vbTextCompare)
                    fCell.Formula = aForm
                End If
            DoEvents
            Next fCell
        End If
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    End Sub


Volevo implementarla rendendola utilizzabile su più fogli simili nello stesso file.
Con mille e più contributi ed aiuti siamo arrivati a questo:
Codice: Seleziona tutto
Sub Marius_Gua_Impr()
    Dim myRangeMitt As Range
    Dim myRangeDest As Range
    Dim mese As String, nRighe As Long, cMese As String
    Set myRangeMitt = Application.InputBox(Prompt:= _
        "Imput Mittente", _
        Title:="InputBox Method", Type:=8)
    If myRangeMitt Is Nothing Then Exit Sub
    Set myRangeDest = Application.InputBox(Prompt:= _
        "Imput Destinatario", _
        Title:="InputBox Method", Type:=8)
    If myRangeDest Is Nothing Then Exit Sub
   
    'copia le righe opportune sia dal Foglio Gua sia dal Foglio Impr
    'e le incolla rispettivamente in Gua ed in Impr
    'e corregge il nome del mese in entrambi i Fogli
    mese = myRangeDest.Value
    nRighe = Application.WorksheetFunction.VLookup(mese, Range("AC3:AE14"), 3, 0)
    cMese = Application.WorksheetFunction.VLookup(mese, Range("AC3:AE14"), 2, 0)
   
    'cambia il nome del mese nelle formule dei due Fogli
    Fgl = "Gua": cc = 0
    Application.ScreenUpdating = False
CicloFogli:
    With Sheets(Fgl)
        Application.CutCopyMode = xlCut
        .Select
        .Range(Cells(3, 2), Cells(nRighe + 2, 13)).Copy   'copia l'intervallo
        .Range(cMese).PasteSpecial                              'incolla l'intervallo
        'costruisce l'intervallo su cui agire per cambiare il mese
        riga = .Range(cMese).Row
        Dim Rng As Range
        Set Rng = .Range(cMese & ":M" & riga + nRighe - 1)
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        If IsNull(Rng.HasFormula) Or Rng.HasFormula Then   'MMM
            For Each fCell In Rng
                aForm = fCell.Formula
                If InStr(1, aForm, myRangeMitt, vbTextCompare) > 0 Then
                    aForm = Replace(aForm, myRangeMitt, myRangeDest, , vbTextCompare)
                    fCell.Formula = aForm
                End If
                DoEvents
            Next fCell
        End If
    End With
    Fgl = "Impr"
    If cc = 1 Then GoTo Xit
    cc = 1
    Set Rng = Nothing
    GoTo CicloFogli
Xit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub

Che funziona correttamente nel file allegato perchè il collegamento è creato ad hoc ed è molto semplice.

Ma nel file master il comportamento cambia. L'idea che mi sono fatto è che mentre la tua sostituisce il testo senza se e senza ma, quella di Mario agganciandosi alle celle , sostituisce il "collegamento", in pratica:
Immagine

Ovviamente se l'aiuto è risolutivo, vorrei con tuo benestare, postarla anche sull'altro forum per chiudere il cerchio...con dignità!!

https://www.dropbox.com/s/czoqpd8zhd3flig/1_PRODUZ_2017_vMariusV0.xlsm?dl=0

Grazie in anticipo.
BG66
Excel2010
BG66
Utente Senior
 
Post: 130
Iscritto il: 20/08/16 07:44

Sponsor
 

Re: Inserire riferimenti per imputbox

Postdi Anthony47 » 17/12/17 20:12

Eh he, bentornato...

Pero' non ho capito (in quanto non l'hai detto) per quale motivo la Sub FormUpdate non puo' essere usata, come e', sugli altri fogli del tuo file; prova a spiegarlo, perche' dalla variante di Marius io non riuscirei a capirlo.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15731
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Inserire riferimenti per inputbox

Postdi BG66 » 18/12/17 10:40

Ciao Anthony,
in pratica ad ogni cambio mese devo:
1) copiare le formule da un mese all'altro
2) impostare con FormUpdate2, il valore da eliminare, valore da sostituire e infine range dove applicarlo
per ogni singolo foglio presente nel file master

A questo punto era nata la voglia/esigenza di automatizare il tutto quindi :
1) la macro copia le formule e modifica il testo in sequenza e per i foglio indicati.

Grazie se puoi.
BG66
Excel2010
BG66
Utente Senior
 
Post: 130
Iscritto il: 20/08/16 07:44

Re: Inserire riferimenti per inputbox

Postdi Anthony47 » 18/12/17 23:16

A questa discussione hai allegato un file che comprende piu' mesi e solo formule per popolare con dati random qualche migliaio di celle.
Le immagini allegate al primo messaggio invece fanno riferimenti a file che ricordo avevano una struttura ben diversa.
Inoltre, se si tratta di fogli mensili, le formule (all'interno del foglio) non dovrebbero cambiare da Gennaio a Febbraio.

Insomma non mi sono fatto ancora un'idea di quale e' il punto di partenza, tantomeno del punto di arrivo.

Comunque per quale aspetto la macro derivata da quanto avevo proposto io (Sub FormUpdate2) non e' idonea per quello che devi fare?
Scusa se torno alla "mia" macro, ma e' quella che so che cosa fa quindi... la capisco piu' facilmente.

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15731
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Inserire riferimenti per inputbox

Postdi BG66 » 19/12/17 09:12

Ciao Anthony,
parto da sotto:
Anthony47 ha scritto:Comunque per quale aspetto la macro derivata da quanto avevo proposto io (Sub FormUpdate2) non e' idonea per quello che devi fare?
..........

Ti confermo che il cuore è proprio la tua macro e vorrei soltanto aggiungergi un plus.

Anthony47 ha scritto:Le immagini allegate al primo messaggio invece fanno riferimenti a file che ricordo avevano una struttura ben diversa.
Inoltre, se si tratta di fogli mensili, le formule (all'interno del foglio) non dovrebbero cambiare da Gennaio a Febbraio.

Infatti il plus è proprio ricopiare le formule che restano immutate nei mesi ma variare la stringa che nella formula punta sia alla cartella mese che al nome del file differenti ( come da prima immagine)

Anthony47 ha scritto:A questa discussione hai allegato un file che comprende piu' mesi e solo formule per popolare con dati random qualche migliaio di celle.

IIl file postato aveva/ha la finalità di aiutarmi a creare le due imputbox che nel ragionamento iniziale doveva sostituire tutte le tue 3 imputbox, cosi da rendere "automatico" sia la copia delle formule che la sostituzione delle stringhe testo nelle stesse.
Aggiungo che i foglio mensili in realtà, come ben ricordi, non sono presenti nel file master ma sono importati e aggregati in un foglio unico ( esempio Gua).

Spero di aver aggiunto qualche informazione che ti possa aiutare. :?:

Grazie
BG66
Excel2010
BG66
Utente Senior
 
Post: 130
Iscritto il: 20/08/16 07:44

Re: Inserire riferimenti per inputbox

Postdi Anthony47 » 20/12/17 14:22

Allora, come ci siamo detti via mp, aspetto un file piu' simile alla situazione reale, con l'indicazione di quali sono le operazioni periodiche che fai e quali cose vorresti automatizzare.

Ti aspetto...
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15731
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Inserire riferimenti per inputbox

Postdi BG66 » 24/12/17 09:42

Buon Natale Anthony,
come da tuo consiglio...riparto da zero.

Cosa faccio

Ad ogni cambio mese per i fogli di lavoro Gua, misure e Impr:
copio le formule del mese precedente in quello nuovo e poi nella stringa sostituisco il nome del mese in ognuno dei fogli sopracitati.
PS prima di FormUpdate2, usando il classico "trova e sostituisci" questa operazione mi costringeva a confermare continuamente i nuovi collegamenti per ogni cella interessata :aaah

Cosa vorrei
Ad ogni cambio mese con una macro fare copia + incolla formule e sostituire testo stringa delle singole celle di ogni foglio di lavoro.

Perchè FormUpdate2 "non và bene":

Interviene nella fase successiva al copia ed incolla e per ogni foglio mi richiede, giustamente vista che l'esigenza iniziale era diversa, le stesse informazioni (testo da eliminare, testo da aggiungere e range di applicazione).

https://www.dropbox.com/s/q3ats692kh1si79/1_PRODUZ_2017_vFORUM1.xlsm?dl=0

PS2 Nel foglio Gua ho lasciato i riferimenti/valori per soli pochi giorni di gennaio per ridurre al minimo i dati sensibili.

Grazie se puoi.
BG66
Excel2010
BG66
Utente Senior
 
Post: 130
Iscritto il: 20/08/16 07:44

Re: Inserire riferimenti per inputbox

Postdi Anthony47 » 24/12/17 21:04

Per oggi contraccambio gli auguri nei prox giorni leggero' anche tutto il resto :D e qualcosa verra' fuori...

Buone Feste
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15731
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Inserire riferimenti per inputbox

Postdi BG66 » 01/01/18 08:23

Ciao Anthony,
spero che i brindisi delle feste abbiano lasciato pochi strascichi... ;)
BG66
Excel2010
BG66
Utente Senior
 
Post: 130
Iscritto il: 20/08/16 07:44

Re: Inserire riferimenti per inputbox

Postdi Anthony47 » 02/01/18 20:08

Ha ha, grazie Gene per la premura; OGGI posso confermare che gli strascichi sono stati tutti temporanei e (apparentemente) superati...
Non ti ho dimenticato; in questi giorni tratto pero' solo argomenti semplici, quelli un po' piu' incasinati (ne ricordo almeno 2 pendenti oltre al tuo) non li ho comunque archiviati.

Quindi... a presto.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15731
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Inserire riferimenti per inputbox

Postdi Anthony47 » 05/01/18 01:21

Allora, finalmente un po' di tregua negli impegni "di stagione"... Vediamo di capire...
Nel foglio GUA ci sono formule del tipo
Codice: Seleziona tutto
=MATR.SOMMA.PRODOTTO(--(RESTO(RIF.RIGA('G:\Forni TNT6-TNT9-TNT10-TNT11\2017\GENNAIO\[T09_GENNAIO_2017.xls]forno_T09'!$L$11:$L$25)+3;7)=0);'G:\Forni TNT6-TNT9-TNT10-TNT11\2017\GENNAIO\[T09_GENNAIO_2017.xls]forno_T09'!$L$11:$L$25)


Nel foglio MISURE:
Codice: Seleziona tutto
=SE.ERRORE(MEDIA('G:\Forni TNT6-TNT9-TNT10-TNT11\2017\GENNAIO\[T09_GENNAIO_2017.xlsm]forno_T09'!$K$5:$K$24);"-")


Nel foglio IMPR:
Codice: Seleziona tutto
='I:\Produzione\2017\[PRODUZIONE_IMPREGNAZIONE_2017.xls]GENNAIO_17'!$R$5


In tutti, in colonna A c'e' la data, che spazia da 1 gen a 31 dic

Mi pare che l'obiettivo sia semplificare la transizione da un mese al successivo.
Questa fase di transizione non mi e' totalmente chiara; visto che il file teoricamente copre l'intero anno, mi immagino che consista nel:
-inserire le formule del nuovo mese
-congelare i valori del mese precedente (ma questo forse lo fai separatamente, quando i dati del mese sono oramai certi e stabili; quindi me lo dimentico)

A questo punto proporrei il seguente approccio, da realizzare tramite una nuova macro:
a) identificare l'ultima riga compilata con formula e l'area del "nuovo mese" da compilare
b) copiare le formule sulla nuova area mensile
c) modificare le formule copiate adattandole al nuovo periodo (nuovo Mese)
NB: per quanto detto al punto a), quanto proposto non andra' bene per gestire la transizione da Dicembre a Gennaio

Le modifiche sono da fare su tre fogli.

Per essere abbastanza certi di modificare solo il modificando, si cercheranno e sostituiranno le stringhe tipo
Codice: Seleziona tutto
"\MesePrecedente\" (es "\GENNAIO\")

"MesePrecedente_Anno" (es "GENNAIO_2017")

"MesePrecedente_Anno" (es "GENNAIO_17")

Nell'ipotesi che quanto detto sia perseguibile e corretto, la macro da usare per la fase descritta sopra puo' essere questa:
Codice: Seleziona tutto
Sub MonthUpdate()
Dim mySh, myMod(1 To 3, 1 To 2) As String, nwMon As Range, lastCol As Long, lastB As Long
Dim cCount As Long, nEoM As Long, EoMR, shName, aForm As String, I As Long
Dim fRan As Range, fCell As Range, mErr As String, cItm As Long
'
mySh = Array("Gua", "misure", "Impr")       '<<< Fogli da modificare
'Start:
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each shName In mySh
    cItm = 0
    Sheets(shName).Select
    lastB = Cells(Rows.Count, "B").End(xlUp).Row
    lastCol = 13    'Cells(lastB, Columns.Count).End(xlToLeft).Column
    nEoM = Application.WorksheetFunction.EoMonth(Cells(lastB, 1), 1)
    EoMR = Application.Match(nEoM, Range("A1:A500"), 0)
    If IsError(EoMR) Then
        mErr = "Ho cercato in A1:A500 la data " & Format(nEoM, "dd-mmm-yyyy") & ", senza trovarla" _
           & vbCrLf & "La procedura non puo' essere completata e viene abortita sul foglio " & shName
        MsgBox (mErr)
        Stop
        Exit Sub
    End If
'Imposta Old & New:
    myMod(1, 1) = "\" & UCase(Format(Cells(lastB, 1), "MMMM")) & "\"
    myMod(1, 2) = "\" & UCase(Format(nEoM, "MMMM")) & "\"
    myMod(2, 1) = "_" & UCase(Format(Cells(lastB, 1), "MMMM")) & "_" & Format(Cells(lastB, 1), "YYYY")
    myMod(2, 2) = "_" & UCase(Format(nEoM, "MMMM")) & "_" & Format(Cells(lastB + 1, 1), "YYYY")
    myMod(3, 1) = UCase(Format(Cells(lastB, 1), "MMMM")) & "_" & Format(Cells(lastB, 1), "YY")
    myMod(3, 2) = UCase(Format(nEoM, "MMMM")) & "_" & Format(Cells(lastB + 1, 1), "YY")
'Copia Formule:
    Set nwMon = Range(Cells(lastB + 1, 2), Cells(EoMR, 2))
    Range(Cells(lastB, 2), Cells(lastB, lastCol)).Copy
    nwMon.PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
'Modifica Formule:
    Set fRan = Union(Range("A1"), nwMon.SpecialCells(xlCellTypeFormulas).Resize(, lastCol))
        For Each fCell In fRan
            If fCell.HasFormula Then
                aForm = fCell.Formula
                For I = 1 To UBound(myMod)
                    If InStr(1, aForm, myMod(I, 1), vbTextCompare) > 0 Then cItm = cItm + 1
                    aForm = Replace(aForm, myMod(I, 1), myMod(I, 2), , , vbTextCompare)
                Next I
                If aForm <> "" Then fCell.Formula = aForm
            End If
        Next fCell
        mErr = mErr & "Completato " & shName & ". Items: " & cItm & vbCrLf
Next shName
'Chiusura:
Application.EnableEvents = True
Application.DisplayAlerts = True
MsgBox (mErr)
End Sub

Si assume che le formule esistenti coprano completamente il mese corrente; quindi le formule saranno copiate nell'area del mese successivo e tutte le formule copiate verranno aggiornate con le sostituzioni descritte.
Ad esempio, nel foglio GUA le formule erano presenti fino riga 10, giorno 8-1-2017. Le formue di riga 10 sono state copiate su righe 11:61 e sono state trattate come ci fosse stata la transizione del mese; cioe' le formule di riga 11, giorno 9-1-2017, farebbero ora (erroneamente) riferimento al mese di FEBBRAIO.

Qualora in un foglio non venga ritrovato in colonna A il "fine mese successivo" la macro viene abortita lasciando il file "in mezzo al guado"; cioe' con alcuni fogli magari gia' stati modificati. Il messaggio informa della situazione di errore, di quali fogli siano stati processati, di quale foglio ha causato l'abort della macro. In questo caso bisognerebbe manualmente eliminare le formule aggiunte sui fogli gia' processati, verificare il perche' dell'errore e correggerlo, ripetere la procedura.
Oppure si potrebbe chiudere il file senza salvarlo, riaprirlo, verificare il perche' dell'errore e correggerlo, ripetere la procedura.

Nel messaggio di completamento viene anche riportato il conteggio di quante modifiche (elementari) siano state fatte nelle formule.

Vedi se quanto detto e quanto codificato fa al tuo caso...

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 15731
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Inserire riferimenti per inputbox

Postdi BG66 » 07/01/18 07:35

Ciao Anthony,
sembra tutto super funzionante e super efficace, nei prossimi giorni cercherò di riportarlo nel file master e farò stress tests più mirati.

A presto per il RISOLTO.

Grazie ancora.
BG66
Excel2010
BG66
Utente Senior
 
Post: 130
Iscritto il: 20/08/16 07:44


Torna a Applicazioni Office Windows


Topic correlati a "Inserire riferimenti per inputbox":


Chi c’è in linea

Visitano il forum: raimea e 25 ospiti