+ Reply to Thread
Results 1 to 24 of 24

Macro to find specific words and compare with a cell

  1. #1
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Macro to find specific words and compare with a cell

    Hi all,

    Hope all is well. I needed a help in creating a macro for the below case:
    Say we have a cell called gender - it can be populated as Male or Female(or M , F). and you have a write up(a sentence) in another cell about a person which will have gender defining words like he, his, him,Mr. can we create a VBA code which checks for these words (he, his, him)and do a comparison with the gender cell. for example, if the gender cell has M and the write up has she,her (which is the opposite *** words)the code should throw an error saying gender not correct.

    Can we do that?

    Thanks in Advance,
    Kinirobin

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to find specific words and compare with a cell

    attach please a sample file

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    UDF

    =GetGender(A1)

    Please Login or Register  to view this content.
    Last edited by jindon; 08-21-2012 at 05:50 PM.

  4. #4
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    Hey Jindon,

    Thanks very much for such a swift reply! but in the formula (=GetGender(A1)) the reference should be for 2 cells rite. Please correct me if I am wrong. I am attaching a test sheet for more clarity Jindon. you have been a Savior for me from the day i joined this forum. I really appreciate wat you have done jindon.

    cheers,
    Kinirobin
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    Quote Originally Posted by kinirobin View Post
    Hey Jindon,

    Thanks very much for such a swift reply! but in the formula (=GetGender(A1)) the reference should be for 2 cells rite. Please correct me if I am wrong.
    How would I know?

    =GetGender(B2,B3)

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    Hey Jindon,

    It works fine !!! thank you so much !! can we include one thing in this code. the result is shown as either true or false. instead of that if the code can show for True = Correct and for False = Mismatch. can that be done?

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    =IFERROR(IF(GetGender(B2,B3),"Correct","Mismatch"),"")

  8. #8
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    Cool........its working fine ! Thank you !

    One basic question: In the VB editor can't we use 2 function codes in the same module. For example the code in this post and the code in the previous post (about finding words in the paragraph) in a same module?
    Last edited by kinirobin; 08-21-2012 at 11:13 PM.

  9. #9
    Registered User
    Join Date
    05-23-2012
    Location
    Japan
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Macro to find specific words and compare with a cell

    Yes you can. you can put 2 function codes on the same module.

  10. #10
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    I tried putting it but non of the code works?

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    1) Be careful, when I opened your file the calculation mode was set to "Manual".
    You need to set it back to "Automatic".

    2) How is the codes in the module look like now?

  12. #12
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    Hi Jindon,

    Sorry to bother you so much ! I have one query regarding this code. I guess we need one alteration. I was doing some trails on the code and found that if the write up has both the pronouns like (he and she) it wont show an error. sending and attached sheet with example....the write up has he and she both in it.
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    Then what is the logical condition of True and False?

    You should also claerly state the condition of 2nd arg for GetGender.

    It was set, based on your post, "M (Male)" or "F (Female)", not M or F.
    Last edited by jindon; 08-22-2012 at 01:29 AM.

  14. #14
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    Ok.... in simple the the code has to Check if gender cell with value "F" has only the female pronouns used in the write up cell. And for gender cell with value "M" has only the male pronouns used in the write up cell. if this is not satisfied the code should throw an error.

  15. #15
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    I am sorry if I am confusing you !!! Please let me know if you did not understand.

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    Please Login or Register  to view this content.
    Last edited by jindon; 08-22-2012 at 01:55 AM.

  17. #17
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    Hey Jindon ! while executing the formula the result is show as #value! I tried only for for getgender code. Is countwords function linked to get gender function?
    Last edited by kinirobin; 08-22-2012 at 02:09 AM.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    If the result is #value!, you have both key words.

  19. #19
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    should I include both the function codes - get gender and countwords for get gender? can't we replace #value! with a word say Error?

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Macro to find specific words and compare with a cell

    Are you going to ask everything to the board?

    Can't you adjust it as I posted?

    =IFERROR(GetGender(B2,B3),"Error")

  21. #21
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    I understand that I am bothering you too much ! I didnt get it at the first instance. you could have been bit polite. thanks !

  22. #22
    Registered User
    Join Date
    05-23-2012
    Location
    Japan
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Macro to find specific words and compare with a cell

    Is the problem already solved?
    Last edited by jonelamora; 08-22-2012 at 02:47 AM.

  23. #23
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    Thank you jonelamora !

  24. #24
    Registered User
    Join Date
    08-06-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Macro to find specific words and compare with a cell

    hey Jindon,

    I have posted a new thread.....can you check it once!

    Finding a Date in a sentence !

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1