More efficient way to Substitute more than 200 cells ?

[charger2000]

I'm preparing a macro where I have to subsitute more than 200 different text strings

The code is the basic:

Please Login or Register  to view this content.

But repeating this code for more than 200 words/strings doesn't look a good idea to me

Is there a more "efficient" way to do this ? "store" a kind of table separately ?

thanks
Nicola

[jeffreybrown]

Ciao Nicola,

Does this thread give you some ideas?

[Fluff13]

Hi & welcome to the board
How about

Please Login or Register  to view this content.

This is based on having a list of old values & a list of new values, change sheet names ranges to suit.

[charger2000]

thanks to both, I'll dig in !

[charger2000]

@Fluff13

at line:

Please Login or Register  to view this content.

instead of "Sheet3", can I put an external link, something like:

Please Login or Register  to view this content.

if so, how's the correct syntax ?

thanks

[Fluff13]

You need to open the file first, which can be done like this

Please Login or Register  to view this content.

[charger2000]

I still have to test this,

quick question: this method works on multiple cells on the same column right ? not on a single cell ?

thanks

[Fluff13]

Yes, it works on the entire column.

[charger2000]

thanks !

Nicola

[charger2000]

forgive me ...
I'm worst than expected at this, just fighting with the syntax and I can't control the result / output properly

I have FOGLIO.XLSX,

in column D, starting from row 2, there are the values that has to be replaced

D
OLDVAL1
OLDVAL2
OLDVAL4
OLDAVL1
OLDVAL1
OLDVAL5
....

these values in D has to be replaced with the values from column B of the file c:\documenti\table.xlsx

A B
OLDVAL1 NEWVAL1
OLDVAL2 NEWVAL2
OLDVAL3 NEWVAL3
.... ....


thanks

[Fluff13]

Ok, just a few questions.
1) Which workbook contains the macro, as both the files you've mentioned are .xlsx files, is it a personal macro workbook?
2) What is the name of the sheet in table.xlsx containing the old & new values?
3) What is the name of the sheet in Foglio.xlsx that contains the values to be changed.

[charger2000]

The macro will be stored in PROCEDURE.XLSX, the file that contains all the Macros (instead of the usual personal.xlsb),
let's call it 'List'
let's call it 'foglio1'

cheers

[Fluff13]

Ok, try this

Please Login or Register  to view this content.

[charger2000]

it works

I had to change A2 and B2 to A1 and B1, because the list starts from row 1 in table.xls

MANY thanks
Nicola

[Fluff13]

Glad to help & thanks for the feedback

[charger2000]

A little help needed...

This Macro is now within "PROCEDURE_SR.xlsm", the "Table" has to be moved on the second sheet of this file, located in C:\Documenti

but no matter how many checks I do on the file name, the macro stops on the line Set Wbk2 = Workbooks("C:\Documenti\PROCEDURE_SR.xlsm"), like if the filename is wrong or the file's missing

Please Login or Register  to view this content.

[charger2000]

I had to remove "C:\Documenti\"



but now it works