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 |
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 |
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 |
All times are GMT +1. The time now is 07:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com