[EXCEL] Funzione sostituisci

[bagus]

Ciao a tiutti, sto perfezionando la macro per la formattaizone del'indirizzo ma non riesco a fare una cosa.
Ho una funzione per la sostituzione di via, corso etc in un range e mi piacerebbe che i valori che devono essere sostituiti vengano prima copiati nella cella precedente.

Ecco il codice.

Codice: Seleziona tutto

Sub Find_Replace()
Dim i, j%

Dim IndStrad(91) As String

IndStrad(1) = "BORGATA "
IndStrad(2) = "BORGO "
IndStrad(3) = "C.SO "
IndStrad(4) = "C/O "
IndStrad(5) = "CALLE "
IndStrad(6) = "CAMPO "
IndStrad(7) = "CANNAREGIO "
IndStrad(8) = "CAPOLUOGO "
IndStrad(9) = "CASTELLO "
IndStrad(10) = "CORSO "
IndStrad(11) = "CSO "
IndStrad(12) = "FRAZ. "
IndStrad(13) = "FRAZIONE "
IndStrad(14) = "GALL. "
IndStrad(15) = "GALLERIA "
IndStrad(16) = "LARGO "
IndStrad(17) = "LOCALITA "
IndStrad(18) = "LUNGO "
IndStrad(19) = "LUNGOMARE "
IndStrad(20) = "P. "
IndStrad(21) = "P.LE "
IndStrad(22) = "P.TA "
IndStrad(23) = "P.TTA "
IndStrad(24) = "P.ZA "
IndStrad(25) = "P.ZZA "
IndStrad(26) = "PIAZZA "
IndStrad(27) = "PIAZZALE "
IndStrad(28) = "PIAZZETTA "
IndStrad(29) = "PRATO "
IndStrad(30) = "PRESSO "
IndStrad(31) = "PROV.LE "
IndStrad(32) = "PZA "
IndStrad(33) = "PZZA "
IndStrad(34) = "RIO "
IndStrad(35) = "RUE "
IndStrad(36) = "S.S. "
IndStrad(37) = "SALITA "
IndStrad(38) = "STAZ. "
IndStrad(39) = "STAZIONE "
IndStrad(40) = "STR. "
IndStrad(41) = "STRADA "
IndStrad(42) = "STRADALE "
IndStrad(43) = "STRADONE "
IndStrad(44) = "V. "
IndStrad(45) = "V.LE "
IndStrad(46) = "VIA "
IndStrad(47) = "VIALE "
IndStrad(48) = "VICOLO "
IndStrad(49) = "VLE "
IndStrad(50) = "ZONA "
IndStrad(51) = "BGO "
IndStrad(52) = "C. "
IndStrad(53) = "CDA "
IndStrad(54) = "CENTRO "
IndStrad(55) = "CIR "
IndStrad(56) = "CIRCONVALLAZIONE "
IndStrad(57) = "CLE "
IndStrad(58) = "CNA "
IndStrad(59) = "CONTRADA "
IndStrad(60) = "CORTILE "
IndStrad(61) = "CSO "
IndStrad(62) = "CTE "
IndStrad(63) = "DSA "
IndStrad(64) = "FR "
IndStrad(65) = "FRZ "
IndStrad(66) = "GAL "
IndStrad(67) = "L.GO "
IndStrad(68) = "LGO "
IndStrad(69) = "LNL "
IndStrad(70) = "LOC "
IndStrad(71) = "PCO "
IndStrad(72) = "PLE "
IndStrad(73) = "VCO "
IndStrad(74) = "STS "
IndStrad(75) = "TRAV "
IndStrad(76) = "TRV. "
IndStrad(77) = "VIC "
IndStrad(78) = "VLO "
IndStrad(79) = "PNO "
IndStrad(80) = "PTTA "
IndStrad(81) = "PZT "
IndStrad(82) = "REG "
IndStrad(83) = "RTE "
IndStrad(84) = "SDA "
IndStrad(85) = "S.RE "
IndStrad(86) = "SAL "
IndStrad(87) = "SLE "
IndStrad(88) = "SP "
IndStrad(89) = "SRE "
IndStrad(90) = "SS "
IndStrad(91) = "STR "

For i = 1 To Sheets.Count
Sheets(i).Select
Range("J2:J2").Select
On Error Resume Next
For j = 1 To 91
Selection.Replace What:=IndStrad(j), Replacement:="I2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next j
Next i
End Sub


qualcuno mi puoi aiutare?
grazie

[Anthony47]

Io farei una cosa radicale, cioe’ copierei tutta la colonna in cui devi fare la sostituzione in una colonna libera; le istruzioni sono facilmente ottenibili tramite un “registra macro”.

Puo' essere idoneo nel tuo caso?
Se non risolve fatti sentire. Ciao.

[bagus]

Grazie per i consigli, ma mi piacerebbe avere una funzione unica.
Pensi che non sia possibile? purtroppo mi sto dedicando da poco a excel e access per lavoro
sono ancora acerbo.
Se si riesce ad inserire nela funzione che ho inserito nel post precedente una sorta di copia della parola che deve essere
sostituita mi farebbe molto piacere.
Thanks

[Anthony47]

mi piacerebbe avere una funzione unica

Immagino che volessi dire “una macro unica”. Infatti io non avevo in mente di farlo con due macro, ma con una aggiunta alla tua macro che, in fase iniziale, copiasse integralmente la colonna su cui vai a fare la sostituzione.
Infatti la mia interpretazione del problema, che tu non esponi, e’ di voler creare una copia di backup dei dati nel caso che l’ esito della “Find_Replace” non fosse proprio perfetto; questa interpretazione nonostante l’ uso dell’ istruzione Range("J2:J2").Select (selezione di una cella specifica, non un range) e la sostituzione dei valori con la stringa “I2”, che non aiutano a capire quale e’ il lavoro finale da fare.

Ora, se ti basta copiare tutta la colonna J metti questa istruzione subito dopo Sheets(i).Select :

Codice: Seleziona tutto

Range("J:J").Copy Destination:=Range("O1")

(copia in colonna O; cambia O1 in XX1 per salvare in colonna XXX).

Se invece hai proprio bisogno di identificare il valore cambiato in ogni riga hai 2 opzioni:
Metodo 1.
Nella colonna P (quella accanto alla O, dove fai il backup dei dati iniziali), su riga 1 inserisci il valore con cui sostituisci le stringhe cercate ("I2" nel tuo caso), e su riga 2 metti la formula

Codice: Seleziona tutto

=SE(O2="";"";SOSTITUISCI(O2;SOSTITUISCI(J2;$P$1;"");""))

Poi copi questa cella verso il basso quanto basta; per le celle modificate la formula visualizzera’ la parte eliminata.

Metodo 2.
Invece del metodo “Replace” usiamo i metodi “Find” e “FindNext”, per rilevare l’ indirizzo in cui avviene il match e poi (1) sostituire la stringa e (2) salvare in una cella affiancata IndStrad(j) corrente; allo scopo dovrebbe andare bene il seguente blocco che sostituisce il tuo originale:

Codice: Seleziona tutto

For i = 1 To Sheets.Count
Sheets(i).Select
'Range("J:J").Copy Destination:=Range("O1")    '<<<1- Scommentare per abilitare copia colonna
For j = 1 To 91
With Range("J:J")             '<<<2- Oppure impostare range
Set FCella = .Find(IndStrad(j), LookIn:=xlValues)
If Not FCella Is Nothing Then
Do
  FCella.Replace What:=IndStrad(j), Replacement:="I2", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
  FCella.Offset(0, 7).Value = IndStrad(j)    '<<< 3- Salva sostituzione in Col O
  Set FCella = .FindNext(FCella)
Loop While Not FCella Is Nothing
End If
End With
Next j
Next i

Guarda le istruzioni marcate <<< per eventuali modifiche come da commenti.

Come sempre, prima di provare fai “due copie di backup” del tuo foglio originale.

Ciao, fai sapere.

[Anthony47]

Errata: '<<< 3- Salva sostituzione in Col O

CORRIGE: '<<< 3- Salva sostituzione in Col Q
(offset di 7 colonne rispetto alla J)

Ciao.

[bagus]

Sei un mito, funziona tutto a meriviglia, ho utilizzato la seconda versione.
Grazie mille
Adesso vorrei provare a fare qualche modifica mi piacerebbe provare a vedere di togliere come è stato fatto per la via anche il numero e metterlo in un altra colonna, provo a vedere se riesco al limite ti disturbo per un aiuto
Mitico..

[bagus]

Ciao, eccomi a richiederti aiuto per la funzione.
Come ti dicevo mi piacerebbe inserire nella funzione la possibilità di estrarre anche il numero e copiarlo nella colonna successiva.
Alcuni numeri hanno la virgola altri no e altri ancora hanno indirizzi particolali, lasciando perdere quelli particolari che posso gestire a mano perche sono pochi, si potrebbe inserire una funzione come la formula =ANNULLA.SPAZI(DESTRA(F1;TROVA(",";F1;1))) ? l'unica cosa è che con questa cerco la virgola ma posso cercare anche lo spazio. visto che la maggior parte degli indirizzi sono via Verdi, 12 o via Verdi 12/a?

grazie

[Anthony47]

E' chiaro che la tecnica usata per estrarre 91 attibuti (un numero alto ma finito) non puo' essere usata anche per estrarre i numeri (teoricamente infiniti, praticamente alquanto variegati; vedasi 12/b, n. 25, 2251, km 10+110, etc). Prima di continuare mi piacerebbe sapere quale e' l' obiettivo e come pensi di raggiungerlo; magari vengono fuori altre tecniche.

Ciao.

[bagus]

E' chiaro che la tecnica usata per estrarre 91 attibuti (un numero alto ma finito) non puo' essere usata anche per estrarre i numeri (teoricamente infiniti, praticamente alquanto variegati; vedasi 12/b, n. 25, 2251, km 10+110, etc). Prima di continuare mi piacerebbe sapere quale e' l' obiettivo e come pensi di raggiungerlo; magari vengono fuori altre tecniche.

Ciao.

Il mio obiettivo è quello di avere una macro che mi normalizza gli indirizzi scritti in una cella suddivisi in 3 celle una per la via una l'indirizzo e una il numero. Capisco che puo' essere un problema torgliere il numero ma nel mio caso specifico ho la maggiorparte dei numeri scritti dopo l'indirizzo con la virgola e con uno spazio (i numeri non hanno spazi tra / e altri caratteri), ci sono alcune eccezzioni come hai identificato ma non solo alcuni hanno pure via rossi angolo via monti 3. Pensavo di utilizzare la funzione destra in visualbasic visto che con le formule non funziona in alfanumerico ma non riesco ad inserirla. Tu cosa consigli? Tieni presente che ho anche una lista di anagrafica corretta che poi posso incrociare per le eccezzioni. Questo mi serve perchè poi devo trasportare tutto in un db mysql. Faccio la macro per permettere ad altri utenti che usano poco excel per permettere anche a loro di usufruirne.
Pensi che sia la strada giusta quella che mi hai consigliato fino ad ora?
Ci sono probelmi ad inserire una funzione in piu' per togliere il numero?
Grazie 1000

[Anthony47]

Se la maggior parte dei numeri sono separati con la virgola, allora usa ”testo in colonna” (Menu /Dati /Testo in colonna; delimitati, Avanti; spunti “virgola”, completi con Ok).
Ma io avrei normalizzatu tutto prima di cominciare a elaborare; ad esempio
-avrei fatto 2 copie di backup
-avrei tolto le virgole presenti sostituendole con Spazio (Trova e Sostituisci)
-non avrei estratto l’ attributo Via/Viale/etc ma l’ avrei reinserito con la virgola aggiunta (FCella.Replace What:=IndStrad(j), Replacement:=left(IndStrada(j),len(IndStrada(j)-1) & ",", LookAt: etc etc);
-avrei replicato l’ operazione fatta sui 91 attributi con le 9 cifre (cercando le stringhe " 1", poi " 2", etc; nota lo spazio prima del numero) e sostituendoli nella stringa originale con la stringa Replacement:="," & Right(IndStrad(j) (in pratica forzando una virgola prima dei numeri civici)
-a questo punto avrei fatto il “Testo in Colonna” e avrei lavorato sulle righe che non vengono divise su 3 colonne, che in prima battuta sono quegli indirizzi che non hanno un prefisso e/o non hanno un numero.

In realta’ poi il piu’ grosso e’ ancora tutto da fare per associare Giuseppe Garibaldi con G. Garibaldi o solo Garibaldi, e tutte le altre varieta’; ma su questo ho da suggerire solo olio di gomito.

Ciao.

[bagus]

Capisco che è una procedura complessa, il lavoro che serve a me è sgrossare il piu che sono proprio i numeri con e senza virgola. Provero' a prendere alcuni tuoi consigli
Grazie 1000 per tutto