Assigning values

A

Azarudeen

Board Regular
Joined
Jun 30, 2014
Messages
80
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Try this:-
Paste this code in the Sheet Module.
Click "Alt +F11", then Paste.
Code: 
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic         [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C2"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]If[/COLOR] Intersect(Target, Rng.Resize(, 2)) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
 [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
  Dic.CompareMode = vbTextCompare
   [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: Dic(Dn.Value) = Dn.Offset(, 1): [COLOR="Navy"]Next[/COLOR]
    [COLOR="Navy"]If[/COLOR] Dic.exists(Target.Value) [COLOR="Navy"]Then[/COLOR] Target = Dic.Item(Target.Value)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick for your quick response.

I am new to VBA. Could you please let me know what value do I have to parse here.
 
Upvote 0
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11"
VB Window appears.
Paste code into VBWindow
Close Vbwindow.
Type any Column "C", symbol in sheet ( Not in columns C or D)
Symbol should be automaticaly changed for related Column "D" letter




Regrds Mick
 
Upvote 0
Thanks Mick,

I tried with the below method for calling and it is working fine.

Public Sub Main()


Call Worksheet_Change((Cells(2, 1)))


End Sub


But I dont want to call the method like this. Everytime I enter some letters in the column and run the macro, it should work.
Could you please help me on that Mick?
 
Upvote 0
With your Data sheet open, you click "Alt + F11" and the VBwindow shows, you then need to paste My code in what should be a blank Vbwindow (apart from "Option Explicit", perhaps).
The first line of code in that page should be
"Private Sub Worksheet_Change(ByVal Target As Range)"

As the code states , this is a "Worsheet Change Event", that means when you change a value in your sheet , the code will run.
You don't need to call it as you have shown above.
 
Upvote 0
Thank for your quick response.

I have only used your code and nothing in the VB window.

When I type any symbol in the Column "A" and tried to run Macro but couldn't as Macro name is not appearing.

Correct me If am wrong If we use Private Sub Worksheet_Change(ByVal Target As Range), we need to parse Range (Target As Range). Correct??

I couldnt find the insert excel option in the above, otherwise I would have attached the my excel here.
 
Upvote 0
I am sorry, I got it now.

Thank you very much for your help.

Is it possible if type 2 or more symbols in the single cell and will it be converted to corresponding letter
 
Upvote 0
I tried but couldn't get.

It is working fine with the single letter.

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA CountIfs - Transpose returning 0 (Boolean) values
Replies
5
Views
409
Fluff
Fluff
markkeith
  • Solved
Function or Formula to get a value for each cell from multiple logical test.
Replies
3
Views
542
markkeith
markkeith
An Quala
  • Solved
Sum up the values from one sheet and add them to another following certain conditions
2
Replies
10
Views
666
Peter_SSs
Peter_SSs
N
  • Question
Export from Excel to Access via VBA based on cell Value
Replies
0
Views
1K
NewPadawan
N
K
  • Question
Variable value does not pass between the 2 books
Replies
1
Views
387
Domenic
D

Forum statistics

Threads
1,214,423
Messages
6,119,398
Members
448,892
Latest member
amjad24

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top