Auto Find/Replace + Marco sort?

[mikeyfear]

Two quick ones. People seem to be calling shovels spades etc if different sheets, so what I'm looking for is something that searches all worksheets in the work book for occurances of A1 (shovel) and replace it with A2 (spades). The list will be about 100 cells long and the idea would be that I could copy a sheet called "replace" with this list in, run it and standardise all the terms in the list within that workbook. Does it sound feasable?

The other query I'm not sure can be done. When I've tidied all these 'names' to a standard one, I want to sort the data (sometimes rows, sometimes columns) alphabeticalled for instance by the A1 to A10, or by A1 to Z1. Simply using the sort option loses all the references as is doesnt change any cells linked to that cell. The only way I could think to make keep the other cells pointing to where they should is to cut and paste/each row/column in alphabetical order manually, but it's taking me forever! Is there another way?

Regards,

Mike

[WinteE]

Hi Mike,

Are you familiar with VBA ?

Erik

[mikeyfear]

Hi Mike,

Are you familiar with VBA ?

Erik

Not in the slightest, bar copying examples from places such of this and 'jiggering' them to attempt to do what I need them to.

If any queries I have would require several hours of work obviously I wouldnt expect anyone to do that, but if (hopefully) it could be done simply in a few lines, or anyone could point me in the direction of examples it would save me hours/days of maunally doing what I'm doing... and I would of course be eternally greatful!

[Simon Lloyd]

Mikeyfear, the code below goes in a standard module (Alt+F11, then right click in the window on the left where you see ThisWorkbook then choose Insert, Module) paste the code in its entirity.

Please Login or Register  to view this content.

My sheet 3 will be the sheet you have list on, the way it works is in column A have the list of words to look for and in column B have the words you want to change them for, so in A1 if you had Spade then in B1 you must have Shovel, when you run the code above it will then change Spade to Shovel, you can of course add a button to your worksheet or toolbar and assign this macro or from the window where you pasted the code click the run button or from excels toolbar choose Tools, Macro, Macro's and then run the one you want, it is in this option window that you can set a keyboard shortcut to run the macro.

[mikeyfear]

Mikeyfear, the code below goes in a standard module (Alt+F11, then right click in the window on the left where you see ThisWorkbook then choose Insert, Module) paste the code in its entirity.

Please Login or Register  to view this content.

My sheet 3 will be the sheet you have list on, the way it works is in column A have the list of words to look for and in column B have the words you want to change them for, so in A1 if you had Spade then in B1 you must have Shovel, when you run the code above it will then change Spade to Shovel, you can of course add a button to your worksheet or toolbar and assign this macro or from the window where you pasted the code click the run button or from excels toolbar choose Tools, Macro, Macro's and then run the one you want, it is in this option window that you can set a keyboard shortcut to run the macro.

That's cracking, thanks a million!

If I want it to highlight the cells it replaces what do I need to add?

Is the sorting marco I was talking about in the original post viable too or is that a non starter?

Regards,

Mike

[Simon Lloyd]

Sounds like a daft question, but why would you want to highlight those cells? and as for the sort and problems with links i would need to see your workbook or a dummy just like it, you can upload one by using Manage attachments at the bottom of your post window.

[mikeyfear]

Sounds like a daft question, but why would you want to highlight those cells? and as for the sort and problems with links i would need to see your workbook or a dummy just like it, you can upload one by using Manage attachments at the bottom of your post window.

I have a large (and stupidly designed) financial model to, map, put in a logical format, then remodel. With the many many (many!) workbooks, we have silly little things like one person calling a customer xyz, and another xyz ltd, and another xyz group limited. I really want the same name of the customer on every sheet, not only for lookups to be effective but also for clarity and auditability. If for instance A1-ZZ1 is the customer name, I'll run the marco above with my alias list and rename every entry that's in a non standard format... but if say there are 220 names in there, and this marco looks at my list and replaces 210 of them, I need some kind of visual reference to those which remain non standard, i.e using yet another alias that I haven't yet added to my list to replace.

Customers is just one example, it applies to cost centres, even depots, being called one thing in one book and something different in another. If I had hair I'd be pulling it out.

I was thinking if I include the 'correct' names in the replacement macro, and it highlights every cell it replaced.. then those cells which need to be replaced and haven't been replaced will stand out and can be easily added to the alias list (i.e. all A1-ZZ1 goes green, then all is good, any non green entries in that range = an unrecognised customer). Again this could probably be done through conditional formatting etc, but then I have to go in and add this to every sheet/book in the model.

Does that make sense?

I'll work on an example on the sorting problem.

[Simon Lloyd]

To be honest this particular code should not miss any of them, if you find that it doesn't then omit the line indicated:

Please Login or Register  to view this content.

It doesn't select the cells it will colour them Green.

[mikeyfear]

you can upload one by using Manage attachments at the bottom of your post window.

This is a very basic example of the problem. I imagine it's just me being inept because I'm sure this would come up all the time if there wasnt a simple way to resolve it.

[mikeyfear]

This is a very basic example of the problem. I imagine it's just me being inept because I'm sure this would come up all the time if there wasnt a simple way to resolve it.

The way I imagine it being done is something like as follows...

If I add an index row, that'll look up from a list so that customer A has a value 1 customer B a 2 etc... A macro could look for the 1 value in the index row, cut the column and insert to the left of column of say 99 in the index row, then find the 2 value etc. Eventually you'd have the columns sorted in the right order, and all formulas intact.

[jindon]

See if this works.

Please Login or Register  to view this content.

[mikeyfear]

Thanks for your efforts Jindon. When I select the range and the key row to sort, I get a 'subscript out of range error' at the following line, in the private sub:

Please Login or Register  to view this content.

[jindon]

Can you change
1) within a test sub

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) replace entire Sub HSortMA with the following code

Please Login or Register  to view this content.

[mikeyfear]

Still the same error at the same point, I've attached the example including the macro above to see if anyone can spot how to make it work (and make sure its not just be being blonde).

Many thanks,

Mike

[jindon]

OOps

Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[mikeyfear]

Thanks Jindon, I see what that does... it pastes links to the side for the sorted data. Is there anyway to have it just replace the data, in the current place it is with the sorted data, because with the above macro it wouldnt help me with the external links etc, because the sorted data is not in the same place as the original. See the above example if you cant picture what im trying to acheive.

Best regards,

Mike

[jindon]

If you don't want the formula, just delete the following block of code

Please Login or Register  to view this content.