Hi,
As the title says I am trying to assign words to a number.
For example, in one column I want the number 1 to automate into the word ankle, 2 to automate into knee, etc.
So essentially its, 1=ankle, 2=knee, 3=leg... The key point is that I don't want to type in ankle/knee/leg repeatedly, just the number.
Is there a formula in excel for this?
How many words do you need to replace? In this code, I assume there are 3 words to be replaced in array 'aWord,' corresponding to 3 numbers in array 'aNum.'
The input column is column A. Right-click on the tab name, choose 'View Code,' then paste this code into the right frame
PHP Code:Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&, aNum, aWord
If Target.Column <> 1 Then Exit Sub ' 1: column A. adjust to actual column (B=2,...,N=14,...)
aNum = Array(1, 2, 3) ' array of value
aWord = Array("ankle", "knee", "leg") ' array of corresponding words
For i = 0 To UBound(aNum)
If Target = aNum(i) Then
Application.EnableEvents = False
Target.Value = aWord(i)
Application.EnableEvents = True
End If
Next
End Sub
Quang PT
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...umber.1249281/)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes there is. It is called XLOOKUP.
https://exceljet.net/functions/xlookup-function
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
I don't read this as a lookup query - I think the OP wants to type 1 into a cell and for Excel to automatically convert it to the corresponding word.
If Ali's interpretation is correct... this might be a non-VBA workaround. A Named Range called List (CTRL-F3 to view edit):
=Sheet1!$Q$1:INDEX(Sheet1!$Q:$Q,AGGREGATE(14,6,MATCH("Zzzz",Sheet1!$Q:$Q),1))
selects all the possible values. It auto-adjusts if/when you add extra ones. In data validation use = List.
Then (with O365) start typing the name of the organ. If you choose Leg... there's only one beginning with an L, so after you type L, only one alternative is presented. With S, there are 3... once you type St it's narrowed to two. Ste and it's narrowed to one. EITHER keep typing until you get a unique entry OR arrow down at any stage to select the one you want.
The advantage is you don't need to remember any number/phrase connections.
see file.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks