Problema con macro Excel

[Geoline]

Innanzitutto, un saluto agli amministratori ed ai frequantatori del forum.
E' da tempo che lo seguo ed ho trovato tanti utili consigli. Chissà che un giorno non potrò essere altrettanto utile a qualcuno di voi.
Premetto che non sono un utlizzatore di excel nelle sue funzioni più nascoste, ma tra quelli che possono dire di averci fatto cose interessanti, da qualche giorno mi trovo nei guai per una macro che non riesco a sviluppare.
Le macro, come molti degli utenti più umili, prima le registro e poi le affino, ma questa volta, decisamente sono in difficoltà.
Ciò premesso, passo alla descrizione del problema.
- Ho predisposto un certo numero di schede (un foglio per ogni file) che dovranno raccogliere dati su ispezioni per impianti.
- Le schede sono tutte identiche, ed hanno un nome uguale tranne un numero racchiuso tra parentesi le ho create in questo modo in quanto dal file padre ho copiato/incollato ottenendo tutti i 500 file automaticamente e rinominando solo il primo:
Copia (1) di Scheda.xls
Copia (2) di Scheda.xls
Copia (3) di Scheda.xls
- Ho predisposto un riepilogativo che, senza riprendere il layout delle schede (ovviamente) ne raccoglie solo i dati.
L'ho organizzato inserendo nella seconda colonna le voci esplicative dei dati e nelle colonne successive i dati per ogni scheda.

Ovviamente le posizioni dei dati sono uguali per ogni scheda, ma per creare il riepilogativo, dopo aver idirizzato i dati della prima colonna, ho poi copiato la colonna nelle altre 499.

Ora viene il quesito:
vorrei creare una macro che seleziona la colonna D e che cambi nella formula l'indirizzo
='C:\Impianto\SCHEDE\[Copia (1) di Scheda.xls]Scheda'!$R$10"
con
='C:\Impianto\SCHEDE\[Copia (2) di Scheda.xls]Scheda'!$R$10"
Prendendo (2) dalla prima riga della colonna (sarà poi 3, 4 ecc)
si sposti nella colonna successiva, selezioni il range e modifichi l' (1) con il (3) e così via.

Forse sono stato un pò prolisso, ma spero sia almeno chiaro.
Grazie in anticipo a quanto vorranno spendere un pò del loro tempo per me.
;-)

[Geoline]

Segnalo, per quanto potranno ritenerlo utile, un interessante link per la gestione delle lettere nei riferimenti delle colonne:
http://community.visual-basic.it/alex/archive/2005/11/06/16539.aspx
E ne riporto le utili macro indicate (non le ho testate ma mi sembrano ok):

Codice: Seleziona tutto

Private Function Column2Literal(ColumnNumber As Long) As String

    Dim FirstLetter As String
    Dim SecondLetter As String
    FirstLetter = ""
    SecondLetter = ""
    ' Verifica se la colonna richiede una o due lettere per
    ' la descizione
    If ColumnNumber < 27 Then
        ' Se richiede solo una lettere ("A"-"Z")
        ' Aggiunge 64 (ASC("A")-1) al numero di colonna e
        ' riporta la lettera ottenuta
        Column2Literal = Chr(ColumnNumber + 64)
        Exit Function
    End If
   
    ' Naturalmente se la colonna è superiore come numero a
    ' 26, allora significa che dovemo gestire due lettere ("AA"-"IV")
    ' p.s. Non tiene conto del fatto che il numero colonna potrebbe
    ' essere maggiore di 256 (numero massimo colonne)
   
    'La prima lettera si ottiene dividendo per 16 ColumnNumber
    ' di seguito sommando il colito ASC("A")-1 otteniamo la prima lettera
    FirstLetter = Chr(Int(ColumnNumber / 26) + 64)
   
    ' La seconda lettera la ottengo verificando il resto della divisione
    ' di ColumnNumber per 26. Sommando il solito 64 ottengo la seconda lettera
    SecondLetter = Chr((ColumnNumber Mod 26) + 64)
       
    'A questo punto so quale è la stringa corrispondente al numero di colonna.
    Column2Literal = FirstLetter & SecondLetter
   
End Function


Codice: Seleziona tutto

Private Function F01_ColLet(VDM_NumeroColonna As Integer) As String

Dim VDM_PrimaLettera As String: Dim VDM_SecondaLettera As String
VDM_PrimaLettera = Space(1): VDM_SecondaLettera = Space(1)

'--------------------------------------------------------------------------------------------
' Verifica se la colonna richiede una o due lettere per la descrizione
'--------------------------------------------------------------------------------------------

If VDM_NumeroColonna < 27 Then

'------------------------------------------------------------------------------------------
' Se richiede solo una lettera ("A"-"Z") aggiunge 64 (ASC("A")-1 al numero
' di colonna e riporta la lettera ottenuta
'------------------------------------------------------------------------------------------

F01_ColLet = Chr(VDM_NumeroColonna + 64)

Else

'-----------------------------------------------------------------------------------------
' Naturalmente se la colonna é superiore come numero a
' 26, allora significa che dovemo gestire due lettere ("AA"-"IV")
' p.s. Non tiene conto del fatto che il numero colonna potrebbe
' essere maggiore di 256 (numero massimo colonne)
'La prima lettera si ottiene dividendo per 16 VDM_NumeroColonna
' di seguito sommando il colito ASC("A")-1 otteniamo la prima lettera
'-----------------------------------------------------------------------------------------

If VDM_NumeroColonna Mod 26 = 0 Then
VDM_PrimaLettera = Chr(Int((VDM_NumeroColonna - 1) / 26) + 64)
Else
VDM_PrimaLettera = Chr(Int(VDM_NumeroColonna / 26) + 64)
End If

'-----------------------------------------------------------------------------------------
' La seconda lettera la ottengo verificando il resto della divisione
' di VDM_NumeroColonna per 26. Sommando il solito 64 ottengo la
' seconda lettera
'------------------------------------------------------------------------------------------

If VDM_NumeroColonna Mod 26 = 0 Then
VDM_SecondaLettera = Chr((VDM_NumeroColonna Mod 26 + 26) + 64)
Else
VDM_SecondaLettera = Chr((VDM_NumeroColonna Mod 26) + 64)
End If

F01_ColLet = VDM_PrimaLettera + VDM_SecondaLettera
a = 1

[Geoline]

Ho provato a modificare il riepilogo per evitare il problema delle colonne.
Traslando le righe in colonne e viceversa ed ottenendo:

Ho anche inserito un ipertestuale che mi consente (in colonna B) di aprire la scheda nr xx qualora ne avessi necessità.
Il problema è che se con il link l'operazionale & di concatenamento funziona regolarmente, quando lo inserisco nella formula, mi visualizza la formula concatenata, ma non il risultato!!

[Anthony47]

Ciao geoline e benvenuto nel forum.
Scartiamo per il tuo problema l' uso di Indiretto, che richiede che i file target siano aperti per funzionare correttamente; suggerisco pertanto la seguente proceura:
-Hai in C1, D1, E1 e successivi hai la sequenza 1, 2, 3 etc (suffisso del nome file)
-metti in C4 la formula

Codice: Seleziona tutto

="#'C:\Impianto\SCHEDE\[Copia ("&B$1&") di Scheda.xls]Scheda'!$R$10"

-copi questa formula in orizzontale, fin dove hai una intestazione in riga 1
-Nota: se quel $R$10 cambia riga per riga, potresti valutare di inserire tale indirizzo in col A (o altra colonna libera) e poi inserire nelle formula di sopra un ulteriore "&" (cioe' un "concatena") verso questa colonna, per poter usare questa formula anche sulle righe successive
-copia la formula di C4 in una cella a piacere anche di un altro foglio (sara' la formula di back-up)
-seleziona tutta l' area che contiene le formule (eccetto la formula di backup), copia ed esegui un Incolla-speciale/valori
-sempre con la stessa selezione, fai Contr-maiusc-t ("Trova"), come stringa da trovare scrivi "#" (senza apici); tab Sostituisci, come stringa di sostituzione segna "=" (senza apici); premi Sostituisci tutto.
In questo modo trasformerai quelle stringhe in formule; se durante questa fase ti si apre la finestra di dialogo "Aggiorna valori: nome del file corrente" allora significa che quel file non e' stato trovato; premere "Annulla" per passare al successivo.

Se hai bisogno di aggiugere nuovi collegamenti (per nuovi file, o nuove righe), parti dalla formula di backup ricopiandola nelle colonne che ti serve e ripetendo il processo.
Ti anticipo che il tempo per estrarre N dati da 500 file chiusi sara' lunghetto, ma un riepilogo val bene un' attesa col caffe'.

Ciao.

[Geoline]

Innanzitutto Grazie per la risposta.
Ci ho pestato la testa un pò ed ho trovato un rimedio diverso.
- Ho rinominato tutte le schede (file) direttamente con un numero.
(tanto, dopo averle copiate, le ho semplicemente rinominate con un SW FREE ;-))

- Ho inserito nella prima colonna il nome della scheda (numero di tre cifre) ma in formato stringa
('001)
- I riferimenti delle celle li ho modificati nel formato RC, ed avendoli nella prima riga ( e dovendo essere uguali per tutte le righe ma differenti nel nome della scheda dalla quale prelevare i dati), ho copiato/incollato la prima riga in tutte le altre 499 trovandomi tutti i riferimenti alla scheda 001.xls

- a questo punto, si trattava di rinominare ='C:\Impianto\SCHEDE\[001.xls]Scheda'!R10C18 sostituendo a 001 il relativo nome della scheda.
Ho risolto con questa macro che in circa 15 secondi fà il suo porco lavoro ;-))

Codice: Seleziona tutto

Sub Sostituzione()
'
' Sostituzione Macro
' Macro registrata il 03/05/2009 da Geoline
'
' Scelta rapida da tastiera: CTRL+k
'
    Dim a As Integer
    Dim cellname As String
    For a = 6 To 504
    cellname = Range("A" & a)
    Rows(a).Select
    Selection.Replace What:="001", Replacement:= _
        cellname, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False
    Next
End Sub


Non ho ritenuto neppure di spiegarla da quanto è facile.


Spero possa essere d'aiuto a qualcun altro.
Ancora grazie e buona serata.

Geo

[Geoline]

Non vedo come editare il messaggio appena inserito.
Ne inserisco un altro.

Intendo precisare che nella seconda colonna (laddove noterete "Apri") ho inserito il link ipertestuale che mi apre la relativa scheda qualora lo desiderassi, senza doverla cercare.

Spero di non essere stato troppo prolisso e sufficientemente chiaro.
Ciao