Macro to find specific words and compare with a cell

[kinirobin]

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

[patel45]

attach please a sample file

[jindon]

UDF

=GetGender(A1)

Please Login or Register  to view this content.

[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. 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

[jindon]

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.

[kinirobin]

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?

[jindon]

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

[kinirobin]

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?

[jonelamora]

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

[kinirobin]

I tried putting it but non of the code works?

[jindon]

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?

[kinirobin]

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.

[jindon]

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.

[kinirobin]

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.

[kinirobin]

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

[jindon]

Please Login or Register  to view this content.

[kinirobin]

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?

[jindon]

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

[kinirobin]

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

[jindon]

Are you going to ask everything to the board?

Can't you adjust it as I posted?

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

[kinirobin]

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

[jonelamora]

Is the problem already solved?

[kinirobin]

Thank you jonelamora !

[kinirobin]

hey Jindon,

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

Finding a Date in a sentence !