# Macro to find specific words and compare with a cell

1. ## 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?

Kinirobin

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

UDF

=GetGender(A1)

``Please Login or Register  to view this content.``

4. ## 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

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

Originally Posted by kinirobin
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. ## 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. ## Re: Macro to find specific words and compare with a cell

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

8. ## 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?

9. ## 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. ## Re: Macro to find specific words and compare with a cell

I tried putting it but non of the code works?

11. ## 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. ## 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.

13. ## 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.

14. ## 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. ## 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. ## Re: Macro to find specific words and compare with a cell

``Please Login or Register  to view this content.``

17. ## 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?

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

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

19. ## 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. ## 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. ## 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 !

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

Thank you jonelamora !

24. ## 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 !

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

#### 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