Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A letter to generat a number when entered into anywhere on the spr
I would like to know how to have a letter generate a number when placed in a
cell randomly on a spreadsheet. For example anytime a P is placed in a cell it would automatically equal 8 Thank You for your assistance. Bill Fitzgerald |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A letter to generat a number when entered into anywhere on the spr
=LOOKUP(C1,A1:B7,B1:B7) C1 is the criteria column A a b c d e f g column B 1 2 3 4 5 6 7 adjust the range and values to what you require -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=536254 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A letter to generat a number when entered into anywhere on the spr
Hi Bill,
If you want a "P" that has been entered into any cell to automatically convert to an "8" in that same cell then you would probably have to use a Worksheet_Change Event Procedure in that worksheet's code module. This example changes "p" or "P" to 8 and "l" or "L" to 12. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ERRORHANDLER Select Case UCase(Target.Value) Case "P" Target.Value = 8 Case "L" Target.Value = 12 End Select Application.EnableEvents = True Exit Sub ERRORHANDLER: Application.EnableEvents = True End Sub Adjust the code to suit your needs just by adding more (or deleting) pairs of lines such as.. Case "some other letter" Target.Value = some other number If case sensitivity is needed then change... Select Case UCase(Target.Value) to... Select Case Target.Value and use the appropriate letter case in the Case statement (Case "P" or Case "p") To get the code in place... 1.Copy it 2. Right click the worksheet's tab. 3. Choose "View code" from the popup 4. Paste the code The workbook now has a macro so ToolsMacroSecurityMedium. Next time the book is opened click on Enable Macros on the Security Warning dialog. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can excel give value in words of number entered in another cell? | Excel Worksheet Functions | |||
How do I convert a given number into a letter? | Excel Worksheet Functions | |||
Assigning a number value to a letter? | Excel Discussion (Misc queries) | |||
How to return a dollar value for a part number entered in a cell | Excel Worksheet Functions | |||
how to add a letter in front of each number in a cell | Excel Discussion (Misc queries) |