VBA macro that can examine if translation of certain words is done right.

Hi.

I've been getting a lot of help just by looking up this forum. This is my second post and I need some help again. : )

I was trying to make VBA macro that can examine if translation of certain words is done right.
I am wondering if anyone can help me with conditions below:

- There are two excel files.
File A is Translation file that has original text in even row of column N(ex,N2,N4,N6...) and translation text in odd row of column N(ex, N3,N5,N7,...).
And file B is VBA macro file that has a list of words in column A (which is english original text) and B(which is other language word).
- Macro search if each original text of file A (which is even row of column N in file A) has the column A words of file B and if there is, it checks if there are the words in the same rows of column B of file B in the translation text (which is odd row of column N in file A)
And if original text has some words of column A and translation text doesn't have matching words of column B, then not the relevant words of column A appear in column U of file A.

For example, if cell N2 of file A is like below,

Centrally located, Grand Villa provides the ideal starting point for your trip to London. All main attractions are reachable on foot like St. Elizabeth’s Cathedral, the Technical Museum and the Garden. Free Wi-Fi is available. Kosino International Airport can be reached in less than 10 kilometres.

and column A and B of file B is like below:

personal	XXXXX
centrally located	OOOOO
on foot	YYYYY

So if there's no OOOOO and YYYYY in N3 of file A, then the result I need in the cell U3 of file A is : centrally located,on foot

and the macro does this up to the last valid column N of file A and shows on the column U of file A.

Thank you so much for taking a look at it and I would appreciate it a lot if someone can help me with this. : )

Thanks a lot, Richard Buttrey.
I've just attached two files.

I attached the two files.

Thank you so much.

Originally Posted by Richard Buttrey

Hi,

Would you attach your two files so that we can see the request in context, and with real data.

No need to attach big files so delete any repetitive rows. Just make sure there is a representative example of all your permutations.
Manually add some test results with a brief note as to how you've arrived at them with reference to specific sheets/ranges.

Hi Hoonjin,

I tried to understand what you want to do but there are a lot of questions before someone can help you achieve what you had in mind.

I did exactly what you described but If you want it to run automatically for all the words in file B it is important to know how you want this to work.

Do you want every second row of File A (M3,M5,M7...) to get checked against the entire list ?


I attached a file where I tried to do it first just the way you asked it to work but I consolidated the file in one workbook. Its a hassle to work with it otherwise and when the code is finished the way you want it is no problem to change it so its for 2 workbooks.

Hope that helps a bit.


File A + B.xlsm

Hi Decar,
Thank you so much. It's great!
But I want every second row of File A (M3,M5,M7...) to get checked against the entire list at a shot as you said.
It's no problem with consolidating them.

Hoonjin

Originally Posted by Decar

Hi Hoonjin,

I tried to understand what you want to do but there are a lot of questions before someone can help you achieve what you had in mind.

I did exactly what you described but If you want it to run automatically for all the words in file B it is important to know how you want this to work.

Do you want every second row of File A (M3,M5,M7...) to get checked against the entire list ?


I attached a file where I tried to do it first just the way you asked it to work but I consolidated the file in one workbook. Its a hassle to work with it otherwise and when the code is finished the way you want it is no problem to change it so its for 2 workbooks.

Hope that helps a bit.


Attachment 284518

Took me a while put good practice.

let me know how it works


File A + B V2.xlsm

Thanks a lot Decar

But the result is different from what I need.
I attached the file with the result I need.
Hope it's easy to understand with this file.

Thanks again.

Hoonjin

Thanks a lot Decar

But the result is different from what I need.
I attached the file with the result I need.
Hope it's easy to understand with this file.

Thanks again.

Hoonjin

hmm I try to understand it first. You basically want to test if any of the words in file b in column B to D are included in the string of n3,n5 etc. ?

hmm k. I think I get closer to understanding it now ^^

I think it can be achieved with 3 loops and the general code would be something like:

Please Login or Register  to view this content.

I found that the function instr should work doing this. Did not think its possible but seems like it is

let me know if this is what you want and what happens when the translation is found to be right or wrong

hm, sorry for not being able to explain easy and simple. and thx for your patience.
below is what i want.

Please Login or Register  to view this content.

when it finds the translation(N3,N5,N7..) to be right, it does nothing about it but if it finds the translation to be wrong, then it shows which words are wrong by showing the relevant words of column A in the cell U (U3,5,7..)

Sorry Decar

I dont think I explained it right.
Here I attached another file with clearer explanation.
Thanks

I tried it .

But Code seems not right.

Please Login or Register  to view this content.

I tried this way,
But the code seems not right in some parts.

Please Login or Register  to view this content.

thanks Decar

Originally Posted by Decar

Hey Hoonjin,

I think I could get this to work but I have no time, sorry. I'm gonna be gone till January.

I'm gonna have a look at this thread in January again and see if it was solved.

regards,

Decar