Trova e sostituisci parte di una formula VBA

[BG66]

Ciao a tutti,
si avvicina il cambio mese e "dovrò" fare le seguenti operazioni:
Parto dalla seguente formula :

Codice: Seleziona tutto

=SE.ERRORE(INDICE('C:\PRODUZIONE\CUCITRICI\2017\[PRODUZIONE_CUCITRICI_GENNAIO_2017.xls]C1 "A"'!$M$4:$M$19;CONFRONTA(RIF.RIGA(A1);'C:\PRODUZIONE\CUCITRICI\2017\[PRODUZIONE_CUCITRICI_GENNAIO_2017.xls]C1 "A"'!$N$4:$N$19;0));0)

nel copiarla ed incollarla in corrispondenza del mese entrante, mi richiede per X volte l'aggiornamento valori riferiti al mese in corso e ahime,la stessa operazione di aggiornamento (sempre per X volte) mi viene poi richiesta quando vado con trova e sostituisci a rimpiazzare il nome "_GENNAIO_" con "_FEBBRAIO_".
Tutto questo per 24 impianti e per 3 righe per ogni giorno del mese (oltre 2000 click del mouse ad operazione!!)

Con VBA si riesce a fare qualcosa?
Qualcuno ha già affrontato e risolto questa problematica?

Grazie in anticipo.

Nel caso serva esempio: https://www.dropbox.com/s/qe9yeb0nqdkl94h/cambio%20data.xlsx?dl=0

PS Problema similare è stato postato in altro forum ma senza ottenere risposta.

[Anthony47]

Non sono certo di aver colto il problema, ma una macro come questa dovrebbe fare il lavoro:

Codice: Seleziona tutto

Sub FormUpdate()
Dim fCell As Range, aForm As String, oPart As String, nPart As String
Dim Ws As Worksheet
'
oPart = "_GENNAIO_"        '<<< Valore da sostituire
nPart = "_FEBBRAIO_"       '<<< Nuovo valore da inserire
'
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each Ws In Worksheets
    Ws.Select
    If IsNull(Cells.HasFormula) Then
        For Each fCell In Union(Range("A1"), Cells.SpecialCells(xlCellTypeFormulas))
            aForm = fCell.Formula
            If InStr(1, aForm, oPart, vbTextCompare) > 0 Then
'                fCell.Select
                aForm = Replace(aForm, oPart, nPart, , , vbTextCompare)
                fCell.Formula = aForm
            End If
        DoEvents
        Next fCell
    End If
Next Ws
Sheets(1).Select
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub


Le righe da personalizzare sono le due marcate <<<; le ho lasciate da compilare a mano perche' mi pare che il problema da superare fosse un'altro; e comunque cosi' organizzata la macro puo' sostituire qualsiasi termine generico con un altro.

Potresti inserire solo la parte iniziale e calcolare la nuova parte:

Codice: Seleziona tutto

oPart = "_DICEMBRE_2017"        '<<< Valore da sostituire
'
nPart = "_" & UCase(Format(Application.WorksheetFunction.EoMonth(DateValue("1" & Replace(oPart, "_", "/")), 1), "mmmm_YYYY"))

In questo caso pero' si potranno sostituire solo termini legati a mesi/anni consecutivi formattati in quello specifico modo.

Ciao

[BG66]

Ciao Anthony,
tenuto conto che dalle prime prove sembra funzionare bene (domani in ufficio ci sarà la prova del 9).
Per imparare ho provato ad intervenire su quello che tu volutamente hai lasciato manuale ma NON ci sono riuscito.

In pratica avrei voluto inserire delle inputboxes per
1) selezionare il range da modificare
2) indicare il mese da sostituire
3) indicare il nuovo mese

La tua macro post mio punto 1 è diventata cosi ma l'indicazione viene bellamente ignorata

Codice: Seleziona tutto

Sub FormUpdate()
Dim fCell As Range, aForm As String, oPart As String, nPart As String
'Dim Ws As Worksheet
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Selezione intervallo:", _
Title:="Intervallo da modificare", Type:=8)
'
oPart = "_GENNAIO_"        '<<< Valore da sostituire
nPart = "_FEBBRAIO_"       '<<< Nuovo valore da inserire
'
Application.DisplayAlerts = False
Application.EnableEvents = False
For Each rng In selection
    rng.Select
    If IsNull(Cells.HasFormula) Then
        For Each fCell In Union(Range("A1"), Cells.SpecialCells(xlCellTypeFormulas))
            aForm = fCell.Formula
            If InStr(1, aForm, oPart, vbTextCompare) > 0 Then
'                fCell.Select
                aForm = Replace(aForm, oPart, nPart, , , vbTextCompare)
                fCell.Formula = aForm
            End If
        DoEvents
        Next fCell
    End If
Next rng
Sheets(1).Select
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Grazie per la fatica che fai nell'aiutarmi a comprendere.

PS Non ho utilizzato il tuo secondo suggerimento perchè "un'automaticità cosi spinta" in questo caso, credo, che non mi dia valore aggiunto (una volta modificato il mese di riferimento, i valori catturati si implementano giornalmente ma poi restano tali fino all'anno successivo). Comunque è gradito tuo pensiero anche in tal senso.

[Anthony47]

Il codice che ti avevo proposto modificava le formule in tutti i fogli del workbook; vedo che piuttosto ti interessa impostare un'area da modificare (cosa che ha senso se invece altre formule le vuoi lasciare con i vecchi riferimenti, altrimenti non risparmi granche'; inoltre l'intervento manuale e' sempre aperto a errori).
Comunque la macro modificata che consente di scegliere tramite Inputbox l'area da modificare e' questa:

Codice: Seleziona tutto

Sub FormUpdate2()
Dim fCell As Range, aForm As String, oPart As String, nPart As String
Dim Ws As Worksheet
'
oPart = "_GENNAIO_"        '<<< Valore da sostituire
nPart = "_FEBBRAIO_"       '<<< Nuovo valore da inserire
'

Dim Rng As Range
Set Rng = Application.InputBox(Prompt:="Selezione intervallo:", _
Title:="Intervallo da modificare", Type:=8)

Application.DisplayAlerts = False
Application.EnableEvents = False
'For Each Ws In Worksheets
'    Ws.Select
    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
'                fCell.Select
                aForm = Replace(aForm, oPart, nPart, , , vbTextCompare)
                fCell.Formula = aForm
            End If
        DoEvents
        Next fCell
    End If
'Next Ws
'Sheets(1).Select
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

Le righe modificate sono rimaste nel codice ma "commentate" oppure sono marcate MMM

Ciao

[BG66]

Ciao Anthony,
ovviamente funziona alla grande.
Ma se hai voglia...un approfondimento e un supplemento d'aiuto:

.... vedo che piuttosto ti interessa impostare un'area da modificare (cosa che ha senso se invece altre formule le vuoi lasciare con i vecchi riferimenti, altrimenti non risparmi granche'; inoltre l'intervento manuale e' sempre aperto a errori).
...

Quindi copiare le formule contenute, ad esempio, nei primi 31 giorni di gennaio e incollarle in marzo, poi grazie alla tua macro modificare la parte dei riferimenti senza dover aggiornare milioni di volte l'indirizzo del file, lasciando intatti i riferimenti dei mesi precedenti, è corretta?
Ripeto che quello che hai fatto funziona a meraviglia ma mi piaceva conoscere il tuo pensiero "teorico" rispetto al quesito.

In merito alle altre due inputbox (inserire il valore da sostituire e quello nuovo) NON sono riuscito ad integrarle nella macro finale.
Le prove fatte prevedevano:
1) l'inserimento delle righe di script prima della richiesta del range selezionato:

Codice: Seleziona tutto

Set Rng = Application.InputBox(Prompt:="Selezione intervallo:", _
Title:="Intervallo da modificare", Type:=8)

2) Settare oPart e nPart = Application.InputBox(Prompt:="Valore etc:", _
Title:="Valore etc", Type:=8)....
Principio sbagliato??

[Anthony47]

Credo che modificare via macro una parte delle formule sia un buon metodo.

Devo anche ricordarti (forse avrei dovuto pensarci prima), che e' anche possibile lavorare tramite "Modifica collegamenti" (accessibile dal "backoffice", cioe' l'area di comandi disponibile sotto il tab File).
In questo modo la sostituzione e' globale su tutto il file, e non so se questo e' accettabile, con un unico comando.

Quanto all'uso di InputBox anche per le stringhe da sostituire, puoi usare ad esempio:

Codice: Seleziona tutto

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


Ma e' anche opportuno modificare la dichiarazione delle due variabili in "as Variant":

Codice: Seleziona tutto

Dim fCell As Range, aForm As String, oPart As Variant, nPart As Variant

Ciao

[BG66]

[RISOLTO]
Ciao Anthony,
mi hai restituito ore di lavoro da usare in maniera più proficua.

Degna chiusura è postare lo script finale:

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


Grazie mille e alla prossima