| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
VBA??
Is there any way i can input certain numbers and they change to words. for
example. 2001 Omagh 2002 Craigavon 2027 Housing Benefit 2036 IT Replacment any help please thanks monty |
|
#2
|
|||
|
|||
|
If you mean you want to enter 2001 in cell A1 and have "Omagh"
magically appear in its place when you hit the return key, I don't think it can be done. However, if you enter 2001 in cell A1 and have a VLOOKUP formula in cell B1, with reference data stored elsewhere in the sprdsht, that is easily done. |
|
#3
|
|||
|
|||
|
Monty
How many substitutes do you have? You are probably best to set up a separate table with the substitutes and then use a VLOOKUP formula to pick them up e.g. if you are entering the data in A1 in B1 enter =IF(A1="","",VLOOKUP(A1,EnterTheAddressOfYourListH ere,2,FALSE)) This will put nothing if there is not an entry in A1 and if there is it will look up the text based on the number. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Monty" wrote in message ... Is there any way i can input certain numbers and they change to words. for example. 2001 Omagh 2002 Craigavon 2027 Housing Benefit 2036 IT Replacment any help please thanks monty |
|
#4
|
|||
|
|||
|
Monty
Your subject line is "VBA??" Unless you use a helper column and a Lookup formula or AutoCorrect VBA might be easiest. Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Dim vRngInput As Variant On Error GoTo enditall Application.EnableEvents = False Set vRngInput = Intersect(Target, Range("A:A")) If vRngInput Is Nothing Then Exit Sub For Each Rng In vRngInput Select Case Rng.Value Case 2001: Rng.Value = "Omagh" Case 2002: Rng.Value = "Craigavon" Case 2027: Rng.Value = "Housing Benefit" Case 2036: Rng.Value = "IT Replacement" End Select Next Rng enditall: Application.EnableEvents = True End Sub Right-click on the worksheet tab and "View Code". Copy/paste the above event code in there. As written it operates on Column A. Enter 2001 in any cell in Column A to return Omagh in that cell. Gord Dibben Excel MVP On Wed, 12 Jan 2005 11:31:08 -0800, "Monty" wrote: Is there any way i can input certain numbers and they change to words. for example. 2001 Omagh 2002 Craigavon 2027 Housing Benefit 2036 IT Replacment any help please thanks monty |
|
#5
|
|||
|
|||
|
Hi
Yes. menu Tools Autocorrect options will do this for you if you set it up properly. HTH. Best wishes Harald. "Monty" skrev i melding ... Is there any way i can input certain numbers and they change to words. for example. 2001 Omagh 2002 Craigavon 2027 Housing Benefit 2036 IT Replacment any help please thanks monty |
|
#6
|
|||
|
|||
|
i have tryed this however nothing happens??
"Gord Dibben" wrote: Monty Your subject line is "VBA??" Unless you use a helper column and a Lookup formula or AutoCorrect VBA might be easiest. Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Dim vRngInput As Variant On Error GoTo enditall Application.EnableEvents = False Set vRngInput = Intersect(Target, Range("A:A")) If vRngInput Is Nothing Then Exit Sub For Each Rng In vRngInput Select Case Rng.Value Case 2001: Rng.Value = "Omagh" Case 2002: Rng.Value = "Craigavon" Case 2027: Rng.Value = "Housing Benefit" Case 2036: Rng.Value = "IT Replacement" End Select Next Rng enditall: Application.EnableEvents = True End Sub Right-click on the worksheet tab and "View Code". Copy/paste the above event code in there. As written it operates on Column A. Enter 2001 in any cell in Column A to return Omagh in that cell. Gord Dibben Excel MVP On Wed, 12 Jan 2005 11:31:08 -0800, "Monty" wrote: Is there any way i can input certain numbers and they change to words. for example. 2001 Omagh 2002 Craigavon 2027 Housing Benefit 2036 IT Replacment any help please thanks monty |
|
#7
|
|||
|
|||
|
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
