Assigning values

[Azarudeen]

Hi All,

I have a excel like below in the C column and D column

C D

ا A
ء A
ب B
ت T
ث TH
ج J
ح H
خ KH
د D
ذ DH
ر R
ز Z
س S
ش SH
ص S
ض D
ط T
ظ Z
ع A
غ GH
ف F
ق Q
ك KH
ل L
م M
ن N
ه H
ة H
و W
ي Y
ى Y
لا LA
ال AL


I want to write a macro that whenever I type ا this needs to be changed to A in the column A and B.
If I change/add anything in C column, it should work.

Can someone help me on this.

[oeldere]

Please Login or Register  to view this content.

[Azarudeen]

Thanks oeldere for your quick response.

When I use the above in A1 cell, it is not giving circular reference warning and If I click Ok, then it shows 0(Zero) in the A1 cell

[oeldere]

I didn't notice (sorry for that) your data where in column C and D.

In that case use the follow formula.

a1

Please Login or Register  to view this content.

[Azarudeen]

Thank you very much Oeldere. Its working fine.

But I want a write a VBA macro so that it can be worked. Could you please help me to do it.

[Azarudeen]

Below is my code

Please Login or Register  to view this content.

It is working fine with the single letter. I want to know, Is it possible if type 2 or more symbols in the single cell and will it be converted to corresponding letters?

Ex: If I type اب in "A" column, it will be converted to AB in "B" column.

[oeldere]

e.g. with this no VBA solution.

=if(len(a1)=1,vlookup(a1,c1:D40,2,0),if(len(a1)=2,vlookup(left(a1,1),c1:D40,2,0)&vlookup(right(a1,1),c1:D40,2,0),"")

and drag down

[romperstomper]

FYI, cross-posted here.

[Azarudeen]

Thanks for your quick response Oeldere.

It is working fine with only two letters. Ex: If I type morethan two letters, it is not giving the output.

I would like to know is it possible to write the same in VBA code?

[oeldere]

Not my part of skills, maybe another forummember.

BTW, Please don't crosspost.

[Fotis1991]

For second time today i am warning you to take a look to forum rules.

This is my last warning. Next time you'll be a banned user.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[Azarudeen]

I am sorry for that. I didn't see the forum rules.

This is urgent that is the reason I posted there also.

Henceforth this wont be happened.