Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel script question -- numbers to words
If you mean you have a list of words/phrases and they each have
corresponding number, you could just use a lookup table (i.e. using th VLOOKUP() function). Search for VLOOKUP in the Help file and tha should get you started. -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel script question -- numbers to words
hi all
i know very little about excel so i don't even know if this is possible i would like to be able to fill in a sheet wit numbers and the numbers then get translated into a prescribed word or phrase. I have over 50 phrases and will need to do it over 10 times (diferently each time) is there a way to get the numbers to translate to words as i type or a global 'update' to tranlate? hope someone can help thanks bronach |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel script question -- numbers to words
You would need a VBA macro to automatically translate what you type into
something else. If you are having problems with worksheet functions, macros should probably be reserved for later, when you are more "fluent" with Excel. On Wed, 11 Aug 2004 19:40:38 GMT, bronach wrote: On Wed, 11 Aug 2004 18:58:09 +0100, kkknie wrote (in message ): If you mean you have a list of words/phrases and they each have acorresponding number, you could just use a lookup table (i.e. using theVLOOKUP() function). Search for VLOOKUP in the Help file and thatshould get you started. K---Message posted from http://www.ExcelForum.com/ thanks for the quick reply hmm , not sure. what i want is to be able to type a bunch of numbers in one sheet in different columns (representing nesting) and instead of the number the word appears ... am i making myself clear? i had a look at vlookup, but i can't undersatand it. i think it might be right but the explanation of how VLOOKUP works only confused me. bronach |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel script question -- numbers to words
In a generous mood today, so here goes:
Paste the following code in the worksheet code module for the sheet yo will be entering the numbers. (Right click on sheet tab and select vie code to get there.) Code ------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then 'Only one cell changed If Target.Row = 1 And Target.Column = 1 And Target.Column <= 10 Then 'A1 through A10 Application.EnableEvents = False 'Disable events Target.Value = Application.WorksheetFunction.VLookup(Target.Value , Sheets("Sheet2").Range("A1:B4"), 2) Application.EnableEvents = True 'Reenable events End If End If End Su ------------------- This is an event procedure which will run whenever a cell is changed o this worksheet. I'll take you through it. 1. Check to see if only one cell was changed (count=1) 2. Check to see if in the range A1-A10. You can change this b changing the .Row and .Column values to fit into your desired range. 3. Disable events. Since we are changing a cell here, we don't wan this event to run again. 4. Target.Value is the changed cell's value. Apply the VLooku function on it using a table on another sheet. I'll explain this a bi more later. 5. Turn event handling back on so it will work next time too. The way VLOOKUP works in your case: =VLOOKUP(LookupValue, LookupTable, ReturnColumn) The lookup value is the number you entered. The lookup table is a set of two columns with the numbers in one an the return values (words/phrases) in the next. The table shoud b sorted by number. The return column in your case would be 2 (the column with th words/phrases). To change my code to handle your case, you will first need to creat your lookup table on another sheet. Then replace Sheets("Sheet2") wit whatever your sheet name is where the lookup table resides. Replac Range("A1:B4") with the range of your lookup table. Post back with questions, -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel script question -- numbers to words
On Wed, 11 Aug 2004 18:58:09 +0100, kkknie wrote
(in message ): If you mean you have a list of words/phrases and they each have acorresponding number, you could just use a lookup table (i.e. using theVLOOKUP() function). Search for VLOOKUP in the Help file and thatshould get you started. K---Message posted from http://www.ExcelForum.com/ thanks for the quick reply hmm , not sure. what i want is to be able to type a bunch of numbers in one sheet in different columns (representing nesting) and instead of the number the word appears ... am i making myself clear? i had a look at vlookup, but i can't undersatand it. i think it might be right but the explanation of how VLOOKUP works only confused me. bronach |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert numbers to words in Excel | Excel Worksheet Functions | |||
question about some excel restriction script running on ... | Excel Discussion (Misc queries) | |||
question about some excel restriction script running on ... | Excel Discussion (Misc queries) | |||
Can you display numbers as words in excel (eg. 10 as ten)? | Excel Discussion (Misc queries) | |||
How can I change Numbers in WOrds in Excel | Excel Discussion (Misc queries) |