Auto-Correct Formulas ??

  • Thread starter Thread starter cassy01
  • Start date Start date
C

cassy01

i have created a code page, this time the code is in column A and The
Long Name is in Column B.(this sheet is called REFS)

i want it so that when i type R1 in column E it replaces it with the
long name.
( on another sheet)

for example: if i type R1 in column E on Sheet1 it automatically
replaces it with ReferenceABC. This is in the other page called REFS ,
short name in column A (R1) and the long name in column B
(ReferenceABC)

Many Thanks :D
Benn
 
One way:

Put this in the Worksheet code module of Sheet1 (right-click the
worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rFound As Range
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Columns(5)) Is Nothing Then
Set rFound = Worksheets("REFS").Columns(1).Find( _
What:=.Value, _
LookIn:=xlValues, _
Lookat:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then
Application.EnableEvents = False
.Value = rFound.Offset(0, 1).Value
Application.EnableEvents = False
End If
End If
End With
End Sub
 
it doesnt seem to work.

does it matter if i already have a macro working ?? as i have alread
got a macro sorting something ese out ,

if not can i send it to you have you add this for me ???

Many Thanks
Ben
 
Back
Top