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
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
=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
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 Tools>Macro>Security>Medium.
Next time the book is opened click on Enable Macros on the Security
Warning dialog.
Ken Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks