VBA??

[Guest]

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

 

[Dave O]

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.

 

[Nick Hodge]

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,EnterTheAddressOfYourListHere,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
(e-mail address removed)

 

[Gord Dibben]

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

 

[Harald Staff]

Hi

Yes. menu Tools > Autocorrect options will do this for you if you set it up
properly.

HTH. Best wishes Harald.

 

[Guest]

i have tryed this however nothing happens??

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

 

[Don Guillett]

Perhaps you put this macro into a regular module when it needs to go into a
SHEET module.