Reply to mr preet makkar for his query - intersting query

898 views 8 replies

CA CS CIMA Prakash Somani

Landmark Group

23512 Points Joined July 2007

Interesting query and smart solution.

Query:

Dear Friend i want to make an excel sheet. For example i want to give number to a = 1, b =2, c= 3, d=4, e=5 and so on. now whenever i enter a name in cell e.g., i write "ca" it should be displayed as 4 (that is 3 for c and 1 for a) "da: should be displayed as 5 cat shoould be displayed as 24 (3 for c, 1 for a and 20 for t) is there any way to do this in excel?

Answer see attached file. very very simple solution by using just two formula : LEN & IF

Attached File : 25603 1068662 solution.xlsx downloaded: 156 times

Replies (8)

CA CS CIMA Prakash Somani

Landmark Group

23512 Points Joined July 2007

let me know if you have any simpler solution then this one..

Aarush

Delhi

1566 Points Joined August 2008

Attached simplification..

password is 11

hope it works

Aarush

Delhi

1566 Points Joined August 2008

With inbulid of macro { to clear text from c2:ab2}

---------------------------------------------------------

Sub Clear()
'
' Clear Macro
'

'
    Range("C2:AB2").Select
    Selection.ClearContents
    Range("C2").Select
End Sub

CA Ayush Agarwal

Kolkata-Pune-Mumbai

27349 Points Joined October 2007

After Modifying- Its Great Sharing , Really Awesme.....

CA CS CIMA Prakash Somani

Landmark Group

23512 Points Joined July 2007

Very nice gaurav... can you let us know how does you make this macro ? actually i want to learn about creting macro's in vba.

Aarush

Delhi

1566 Points Joined August 2008

Hi Bhayiya (Hope i can call you) .. Well i have never chat with you but i heared a lot about you so thanks for being with us and guide us.. It will be great that i can share something which is / are useful for all members..

Bhaiyya, there are two ways to learn macro, 1. By writing scriptt manually and 2. By recording it... I always use second option.

Attached little Illustrative hope it work.

CA Ayush Agarwal

Kolkata-Pune-Mumbai

27349 Points Joined October 2007

Originally posted by : Gaurav Aggarwal
	Hi Bhayiya (Hope i can call you) .. Well i have never chat with you but i heared a lot about you so thanks for being with us and guide us.. It will be great that i can share something which is / are useful for all members.. Bhaiyya, there are two ways to learn macro, 1. By writing scripttt manually and 2. By recording it... I always use second option. Attached little Illustrative hope it work.

Hi, Gaurav, Means The Step Which We Do After Recording Option Being On Will be Recorded ?????or What ??

Aarush

Delhi

1566 Points Joined August 2008

Hi Ayush,

Yes it will record each steps you take after press "Record Macro", but still some commands are there by which you can modify computerized scripttt for required purpose.