parts of macro aren't working

Hey all,

Parts of the below macro aren't working. For example, if a name is like this:

John Smith R, Sr.
Ann Smith S,


It's supposed to be replaced with:

John Smith R
Ann Smith S

Please Login or Register  to view this content.

It doesn't produce any kind of error message. It simply just doesn't get rid of aforementioned undesirable content, despite me clearing telling it to in the macro.

Thanks for response.

Your code isn't ever touching the data, it's just putting formulas into the cells, then replacing the formula with the resulting value. Also, your second assignment statement is wiping out the first. Also, your formulas are always going to refer to cell A1 regardless of what row the new data is on. Maybe you are unclear on exactly how this kind of code works.

This might be more effective if you modify your VBA to do what you actually want, instead of using VBA to plug in formulas to cells. String manipulation is a little more sophisticated in VBA than what you can do with Excel functions (but only a little).

Here is a start. It is difficult to do this without seeing sample data. This assumes that the data you're starting with is in column A in the last row, and the cleaned-up version goes into column B. I didn't test it because I don't know exactly what your data looks like. I gleaned this from your formulas.

Please Login or Register  to view this content.

Thanks for response. It gives me a compile error right away highlighting "Range" in Range.Cells(lngLastRow, 1).Value
It says "argument not optional"

Originally Posted by johnmerlino

Thanks for response. It gives me a compile error right away highlighting "Range" in Range.Cells(lngLastRow, 1).Value
It says "argument not optional"

I'm sorry, in my rush to publish I didn't test. This will compile, and mostly work. I will check it against your test data and provide any needed updates.

Please Login or Register  to view this content.

Sheet1 is the name of the sheet with the data. If the code is in the same module as the worksheet with the data you don't need "Sheet1." where it appears.

OK, here's a workbook. Click the button to clean up your data.

If you put the macro in PERSONAL.XLSB you will have to rewrite it. It is written to execute in the same book that contains the data. Will revisit when I have some time.