Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Data I receive shows initials of persons. I need those initials to change to
a number when I type in the initials. For instance I have the initials MJ occur over and over and when I type those initials into a cell, I need the cell to say 01, the initials HS need to say 02, the initials SE need to say 03, etc. Any help is appreciated! Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
How far does "etc." go?
For a few you could use event code which would change the values when you enter them. For many you would be better off using a helper cell with a lookup table. Event code similar to this. Edit vals array, nums array and "A1:A100" to suit. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:A100") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then With rr .Value = ival .NumberFormat = "00" End With End If Next End Sub This is sheet event code. Copy then right-click on sheet tab and "View Code". Paste into that sheet module. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote: Data I receive shows initials of persons. I need those initials to change to a number when I type in the initials. For instance I have the initials MJ occur over and over and when I type those initials into a cell, I need the cell to say 01, the initials HS need to say 02, the initials SE need to say 03, etc. Any help is appreciated! Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
I have 25 different persons with initials that need to change to number.
Thank you. "Gord Dibben" wrote: How far does "etc." go? For a few you could use event code which would change the values when you enter them. For many you would be better off using a helper cell with a lookup table. Event code similar to this. Edit vals array, nums array and "A1:A100" to suit. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:A100") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then With rr .Value = ival .NumberFormat = "00" End With End If Next End Sub This is sheet event code. Copy then right-click on sheet tab and "View Code". Paste into that sheet module. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote: Data I receive shows initials of persons. I need those initials to change to a number when I type in the initials. For instance I have the initials MJ occur over and over and when I type those initials into a cell, I need the cell to say 01, the initials HS need to say 02, the initials SE need to say 03, etc. Any help is appreciated! Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
In that case you can expand the event code vals and nums arrays or go with a
helper cell and a VLOOKUP table. Gord On Wed, 4 Jun 2008 05:37:02 -0700, KO wrote: I have 25 different persons with initials that need to change to number. Thank you. "Gord Dibben" wrote: How far does "etc." go? For a few you could use event code which would change the values when you enter them. For many you would be better off using a helper cell with a lookup table. Event code similar to this. Edit vals array, nums array and "A1:A100" to suit. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:A100") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then With rr .Value = ival .NumberFormat = "00" End With End If Next End Sub This is sheet event code. Copy then right-click on sheet tab and "View Code". Paste into that sheet module. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote: Data I receive shows initials of persons. I need those initials to change to a number when I type in the initials. For instance I have the initials MJ occur over and over and when I type those initials into a cell, I need the cell to say 01, the initials HS need to say 02, the initials SE need to say 03, etc. Any help is appreciated! Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Thank you so much for your help.
"Gord Dibben" wrote: In that case you can expand the event code vals and nums arrays or go with a helper cell and a VLOOKUP table. Gord On Wed, 4 Jun 2008 05:37:02 -0700, KO wrote: I have 25 different persons with initials that need to change to number. Thank you. "Gord Dibben" wrote: How far does "etc." go? For a few you could use event code which would change the values when you enter them. For many you would be better off using a helper cell with a lookup table. Event code similar to this. Edit vals array, nums array and "A1:A100" to suit. Private Sub Worksheet_Change(ByVal Target As Range) Set r = Range("A1:A100") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("MJ", "HS", "SE", "HB", "KY", "LA", "OK", "SD", "YZ") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r ival = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then ival = nums(i) End If Next If ival 0 Then With rr .Value = ival .NumberFormat = "00" End With End If Next End Sub This is sheet event code. Copy then right-click on sheet tab and "View Code". Paste into that sheet module. Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 3 Jun 2008 18:56:01 -0700, KO wrote: Data I receive shows initials of persons. I need those initials to change to a number when I type in the initials. For instance I have the initials MJ occur over and over and when I type those initials into a cell, I need the cell to say 01, the initials HS need to say 02, the initials SE need to say 03, etc. Any help is appreciated! Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|