Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is Macros Right?
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? -- KC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is Macros Right?
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 On Dec 27, 2:06*am, E-Assistant wrote: 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? * -- KC |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is Macros Right?
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 On Fri, 26 Dec 2008 06:06:01 -0800, E-Assistant wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros: can you copy macros from one doc to another? | Excel Discussion (Misc queries) | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions |