Parsing data in cell ranges

  • Thread starter Thread starter Consuelo Guenther
  • Start date Start date
C

Consuelo Guenther

Hello everyone!
I am trying to manipulate data in a range of cells in
Excel using a macro.

I need to have the macro search through a column and
verify the data in each cell.

For example, cell A1 has value of "Joe", cell A2 has
value of "Jim D". I need the macro to return A1 with
value of "Joe"(no changes there) and A2 returns a value
of "Jim".

How can I achieve that?
Thanks for advance for your help.
Consuelo :-)
 
There's no simple answer to your question. Your options vary, depending
on the variety of the potential errors you need to fix and how oftenm
you expect to fix them.

If you know in advance exactly what the potential errors are, then you
could create a lookup table of invalid entries and their fixes. A macro
could then scan each entered value, check it against the lookup table,
and return the fix if there was a mistake. This way, every "Jim D" could
become "Jim" or "Jim Davis" or whatever. There's really no limit to the
size of the lookup table you can create, though if you don't expect to
use it often there are diminishing returns -- building an accurate
lookup table takes time.

If the errors are more random, a lookup table won't be much help and
your fixes will become more complicated. It's possible to design VBA
procedures that can scan and fix most anything, but you'll need to at
least identify some consistent patterns in the invalid entries. What
exactly distinguishes a valid entry from an invalid one? Whatever you
do, take care that your "fix" doesn't end up doing more harm than good
by inadvertently altering valid data. Test, test test!!
 
Back
Top