Is Macros Right?

E

E-Assistant

I am working on huge project where I have to input individual definitions
that have a numerical code for each. What i would like to do is set up a
system that would allow me to just type the code and it automatically be
replaced (w/the same cell) by it's definition. Could macros do this, if so
how or would I need to use a formula, if so which one?
 
M

Mike H

Hi,

You would need a macro for that. Create a table of codes and text
values which in the case of this code is in sheet 3 columns A & B.
Then right click the sheet where you want to display this data and
view code and paste this in. It currently looks only for entries in
column 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
On Error GoTo getmeout
Application.EnableEvents = False
LookUpRange = Worksheets("Sheet3").Range("A1:B500").Cells
Target.Value = Application.WorksheetFunction.VLookup(Target.Value,
LookUpRange, 2, False)
Application.EnableEvents = True
Exit Sub
getmeout:
MsgBox "No Value found for that code"
Application.EnableEvents = True
End Sub


Mike
 
G

Gord Dibben

It can be done but sounds a little dangerous to me.

How do you know if you have typed in the correct code number?

You would be better off using a helper column with a VLOOKUP formula to
return the definition.

The lookup table can be on another sheet.

Easier to error-check because the number as well as the definition will be
visible.

To use event code as you asked, you could use a table on Sheet2 in say
G1:H10

This code would go into the sheet module of Sheet1(your entry sheet)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ival As String
Dim R As Range
Set R = Range("A1:A100") 'adjust to suit.
If Intersect(Target, R) Is Nothing Then Exit Sub
If Not IsError(Application.Match(Target.Value, _
Sheets("Sheet2").Range("G1:G10"), 0)) Then
ival = Application.VLookup(Target.Value, _
Sheets("Sheet2").Range("G1:H10"), 2, False)
Target.Value = ival
End If
End Sub


Gord Dibben MS Excel MVP
 
Top