+ Reply to Thread
Results 1 to 12 of 12

Matching Dictionary Terms with Definitions

  1. 03-09-2009, 03:21 PM #1
    luu980
    luu980 is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    50

    Matching Dictionary Terms with Definitions

    Hi,

    I have created a dictionary with technical terminology. It is in excel format with about 10,000 entries. The format of my dictionary ("Sheet1") is in three columns:

    <vocabulary term> <pronunciation> <definition>


    Now, I want to create vocabulary lists using my dictionary. The newly typed up vocabulary list (which only has the term, but not pronunciation or definition), will be copy and pasted into "Sheet2".

    The "Sheet2" typed up vocabulary list is in the format:

    <vocabulary term 1>
    <vocabulary term 2>
    etc...


    I want to run a macro or script to dictionary terms (including pronunciation and definition) from "Sheet1" and put them into my vocabulary list "Sheet2".


    The desired output list in "Sheet2" is:

    <vocabulary term 1> <pronunciation> <definition>
    <vocabulary term 2> <pronunciation> <definition>
    <vocabulary term 3> <pronunciation> <definition>
    etc...


    I would be grateful if someone could help me write a macro to do this?

    Sorry, I am pretty much a complete newbie to this and would greatly appreciate any help that can be provided.

    Thank you!
    Last edited by luu980; 03-09-2009 at 07:58 PM.
    Reply With QuoteRegister To Reply
  2. 03-09-2009, 04:22 PM #2
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Matching Dictionary Terms with Definitions

    This should do the trick:

    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  3. 03-09-2009, 04:40 PM #3
    luu980
    luu980 is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    50

    Re: Matching Dictionary Terms with Definitions

    davegugg,

    Thank you for writing the code. I tried to run it but it gave me a runtime error: 438: object doesn't support this property or method

    I tried changing Application.ScreenUpdation = False to be Application.ScreenUpdating = False, but when I ran it, excel is freezes up / crashes. I tried it with a vocabulary list of 1 items. And a dictionary size of 10 items.


    Also, my dictionary.xls file has a few worksheets with different names, will that affect the execution of the macro? For example, for the dictionary sheet, the sheet is called 'dictionary', then i have a few other sheets with various things. Do I need to alter the code in anyway for it to know to match the dictionary sheet definitions to my 'vocabulary' sheet?

    Also, when I expand my dictionary to 20,000 or 30,000 items do I need to change the code to For i = 1 To 30000 and For j = 1 To 30000?

    Sorry, I don't know anything about programming, so I'm not sure what I need to change.
    Last edited by luu980; 03-09-2009 at 04:57 PM.
    Reply With QuoteRegister To Reply
  4. 03-09-2009, 04:53 PM #4
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Matching Dictionary Terms with Definitions

    You can rename the sheets to your sheet names. What I called Sheets(1), you should have as your dictionary sheet, and Sheets(2) should be your Vocab sheet. I will update so you don't have to change i and j, as your ranges may be dynamic.
    I will post updated code.

    Dave
    Last edited by davegugg; 03-09-2009 at 05:03 PM.
    Reply With QuoteRegister To Reply
  5. 03-09-2009, 05:01 PM #5
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Matching Dictionary Terms with Definitions

    Ok, my mistake, I spelled "Updating" wrong. Here is a new version that will run to whatever your last row is:

    Please Login or Register  to view this content.
    You still have to rename the sheets as your names.

    Dave
    Reply With QuoteRegister To Reply
  6. 03-09-2009, 06:07 PM #6
    luu980
    luu980 is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    50

    Re: Matching Dictionary Terms with Definitions

    Dave,

    Thank you so much!! The code you provided appears to work flawlessly.

    I do have a small problem though, when I globally rename "Sheets(1)" to dictionary in the code, I get an 'object required' error.

    It's strange though, when I leave the code as Sheets(1) and Sheets(2), but I rename my dictionary sheet to Sheet(1) and Sheet(2), the code still runs! I noticed this because I typoed and typed 'sheet' and neglected to type 'sheets'.

    What am I doing wrong in the renaming processs?

    For example I tried renaming Sheets(1) to be Sheets(Dictionary) and Sheets(2) to be Sheets(Vocab_List), then renaming my worksheets Dictionary and Vocab_List respectively. However, when I try to run it, I get a "subscript out of range" error.

    I'm sure I'm going something really stupid and overlooking something very simple. But I've been trying for an hour now and can't still figure it out. ugh!
    Last edited by luu980; 03-09-2009 at 06:23 PM.
    Reply With QuoteRegister To Reply
  7. 03-09-2009, 06:52 PM #7
    digita
    digita is offline
    Registered User
    Join Date
    10-31-2007
    Posts
    34

    Cool Re: Matching Dictionary Terms with Definitions

    Hi Luu980,

    Each sheet within an XL workbook has a unique VBA code name which remains constant. The code name of each sheet is displayed in the project explorer panel within VBE.

    So, assuming sheet2 is the unique code name of sheet #2, if you replace "Sheets(2)" with "Sheet2" the code would run flawlessly regardless what name you have for sheet 2 in XL.

    Hope this helps.
    Reply With QuoteRegister To Reply
  8. 03-09-2009, 07:57 PM #8
    luu980
    luu980 is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    50

    Re: Matching Dictionary Terms with Definitions

    digita,

    Oh I see. Thank you for the help!

    So pretty much means as long as my dictionary sheet is my first sheet and my vocabulary sheet is my second sheet, then the macro will work without a hitch no matter what name I attach to them.
    Reply With QuoteRegister To Reply
  9. 03-10-2009, 06:24 AM #9
    Cheeky Charlie
    Cheeky Charlie is offline
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: Matching Dictionary Terms with Definitions

    yes but it's better to name them and refer to the names - i.e. worksheets("dictionary") - this will stop the code ruining your workbook if the sheets aren't in the right order - remember VBA is not undoable!
    Reply With QuoteRegister To Reply
  10. 03-10-2009, 09:30 AM #10
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Matching Dictionary Terms with Definitions

    Right, it looks like you just didn't put your sheet names in quotations. If your sheet name is dictionary, literally put Sheets("dictionary").
    Reply With QuoteRegister To Reply
  11. 04-02-2009, 06:06 AM #11
    luu980
    luu980 is offline
    Registered User
    Join Date
    03-08-2004
    Posts
    50

    Re: Matching Dictionary Terms with Definitions

    davegugg helped me write a macro to match dictionary terms with definitions awhile back. I need a small modification on it and was wondering if anyone could help tweak the code just a little bit to add in three more cells in each row?

    Originally, I had:
    <vocabulary term> <pronunciation> <definition>

    However, now I need to add to cells to each row:
    <vocabulary term> <pronunciation> <definition> <note 1> <note 2> <note 3>

    Everything else remains the same, I just need to add additional notes in 3 cells.

    I tried sending davegugg a couple of messages a week ago, but haven't had any response, so if anyone could help me with this I'd greatly appreciate it.
    Reply With QuoteRegister To Reply
  12. 04-22-2009, 02:08 PM #12
    davegugg
    davegugg is offline
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Matching Dictionary Terms with Definitions

    Sorry, I've been very busy at work, so I haven't been on here in a while. I think you would want:

    Please Login or Register  to view this content.
    Instead of:

    Please Login or Register  to view this content.
    Or maybe the whole row:

    Please Login or Register  to view this content.
    Not sure if that last one will work.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1