[bigsteve100]

In Excel, I want to create a macro to edit a cell (F2), delete some characters and add some new characters, leaving the rest of the cell data unchanged.

When I record a macro this way and then run it it changes all the data in the cell. Not what I want.

Example: existing data in cells is A1 [alum p 10x13] A2 [alum p 11x14]
after macro runs should be A1 [alum pepsi 10x13] A2 [alum pepsi 11x14]

when i do it i delete p and pepsi and stop but the macro changes my numbers too. not what i want it to do.

Please help.

[Zack Barresse]

Hi there, welcome to the board!

Macros are not intuitive, and you must give it all the logic you use to think of how to change these items. The question becomes what then is your criteria for changing these values? I'm assuming you have other cells you want to change. Is it always the same? Looks like a "p" with a space on either side and you want it changed to "pepsi" with a space on either side. Is that true in all cases? Do you want to run this more than once? Do you want this to be an automatic change? Please provide a little more information about the entire scope of what you're trying to do. Before/after examples are great (just leave the 'how' to us ).

[bigsteve100]

Thanks. Yes i want to repeat it and yes there are multiple cells to change and if there is a "c" in the cell i want to change it to "coke". these are not the real names just examples. again my problem is that i only want to modify certain parts of the cell - not the entire cell data.

[Zack Barresse]

Okay, so do you want to run this manually then? I.e. select a range of cells and run this procedure on? Or do you want this to be done automatically when you edit/change a cell?

Also, we need real examples of your real data. One common misconception about these boards is that you can just give examples that are not related to your data, and it never works out. We need to see your data and have you explain all the possibilities. For example, these two would be very different...

"something c whatever here"

"c whatever here"

The "c" is in the middle on the first one, surrounded by a space on either side. The second "c" is at the far left and has a single space on the right of it but nothing on the left. So we'd need to know everything you want to look at.

[bigsteve100]

Yes. they are in different spots. I need to know how to do it myself. I used to record macro hit the keystrokes i needed and then stop and save the macro and it would run exactly that way. but now when I record and then stop, it duplicates what is in that cell totally, in other words it copies instead of editing the cell.

[Zack Barresse]

Well unfortunately recorded macros don't discern what is in the cell, and certainly not whilst in Edit mode (i.e. pressing F2). That's why you need to define the logic in which you'll want to perform this. We'll have to write the code around your logic.

[Aj_old]

When you record such a macro, I'm almost sure that you'll have something like this:

    Range("A1").Select
    ActiveCell.FormulaR1C1 =         "alum pepsi 10x13"
    Range("A2").Select
    ActiveCell.FormulaR1C1 =         "alum pepsi 11x14"

Instead you should create / record a macro which replaces some values, but to do this, like Zack said you need to have, for example always " p ", and it does not depend on its position as long as you have a space on both sides of "p".


Or for example you have a whole list of products, let say drinks, and you don't have the full name but just and abbreviation of the drink name, like "#ck" for Coca cola, #p for Pepsi, #Dr for Dr. Pepper, and so on, and the abbreviation is a sequence of letters and numbers which always referees to that drink and can not be any thing else.

[slurpee55]

Originally Posted by Aj_old

Or for example you have a whole list of products, let say drinks, and you don't have the full name but just and abbreviation of the drink name, like "#ck" for Coca cola, #p for Pepsi, #Dr for Dr. Pepper, and so on, and the abbreviation is a sequence of letters and numbers which always referees to that drink and can not be any thing else.

Just to add to what these guys are saying - from Aj's note, can you see why it is important for the coders to know what you are looking at specifically and what, specifically you want to replace that data with?
If you don't, then, code replacing all "p"s with "pepsi" could give you a sentence like "repepsilacing all "pepsi"s with "pepsiepepsisi".
So it is vital that they know how the data in a cell is laid out.

[Zack Barresse]

Of course, there is always the great standard Find/Replace from the Edit menu (or Editing group on the Home tab in 2007).

In reality, if done via code, this could be as simple as finding the logic and performing the replacement, or even as unique as regular expressions. Definitely need to know more about what constitutes what for replacement though.