+ Reply to Thread
Results 1 to 4 of 4

DIY Spell Check Table To Correct Data Errors using VBA

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    20

    DIY Spell Check Table To Correct Data Errors using VBA

    I have a very large data entry set that often has misspellings that the built in spell check is unable to adequately correct.

    I have attached a sample spreadsheet with 2 sheets, one for the "fixed" data (where I would like a macro to correct those spellings), and a spell check lookup sheet where I've started to compile a table with all of the incorrect spellings in column A and the correct spelling in column B.

    My question is, does anyone have some sample VBA code that can get me started in the right direction to use the spell checker table I've started to search through all of the data entered in the "Fixed" sheet and automatically start replacing any incorrect spellings with the correct spellings?

    Thank you in advance for your help!!
    Attached Files Attached Files
    Last edited by edneal2; 06-14-2017 at 10:30 AM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,542

    Re: DIY Spell Check Table To Correct Data Errors using VBA

    You could use a VLOOKUP in a helper column. =IFERROR([Country],VLOOKUP([Country],SpellCheckTable,2,False),[Country]).

    Or if you really want to go the VBA route, you could do a change event and use the find command to accomplish the same thing. This would be more interactive - the user types in a name, and it is changed in the same cell where it is entered.

    Which way would you like to go?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,377

    Re: DIY Spell Check Table To Correct Data Errors using VBA

    Hi Ed,

    This will replace only if the Incorrect is exactly represented in the cell:

    Please Login or Register  to view this content.
    This will replace if any "word" matches the Incorrect:

    Please Login or Register  to view this content.
    * You'd need a separate list for the second chore
    Last edited by xladept; 06-15-2017 at 12:13 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,542

    Re: DIY Spell Check Table To Correct Data Errors using VBA

    Here is a version that uses VBA. First, I converted the data to tables since tables know how big they are. This makes it easier to limit the search on the cells, both for entered countries and the lookup.

    The program uses a change event. So when you enter a country, it looks for the name of the country in the Incorrect column. If it finds it, it replaces it with the value in the Correct column. Otherwise it leaves the value as is.

    You might save yourself the trouble in the first case by providing a list of valid country names in a table (or list), and use data validation. Data validation could be set up for the following:
    - Not allow an entry that is not on the list
    - Allow an entry that is not on the list, if you confirm it.
    - Allow an entry that is not on the list, and just give you a message
    http://www.utteraccess.com/wiki/Data_Validation

    Another feature of tables is that when you enter a new line of data, it copies down formulas, formats and validations automatically.

    Data validation will work if you are pretty sure you have a complete list of countries. A web search may turn up a database that you can copy and paste.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing Spell Check language of Vba Spell Checker
    By jhelliar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2021, 03:51 AM
  2. Excel VBA, spell check to ignore data validation and text in shapes
    By Quagmire in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2016, 03:43 PM
  3. Check for errors and correct them based on specific logic
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2016, 12:33 PM
  4. Replies: 1
    Last Post: 10-20-2014, 11:01 AM
  5. [SOLVED] Check cells are correct format and contain correct data
    By rikosborne in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-26-2014, 02:53 PM
  6. Replies: 0
    Last Post: 11-18-2005, 03:15 PM
  7. Replies: 2
    Last Post: 05-09-2005, 03:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1