VLookup in Code
 

Steven,

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo NotFound:
With Target
.Value = Application.WorksheetFunction.VLookup( _
.Value, Range("Categories"), 2, False)
End With
GoTo Found:
NotFound:
Target.Value = ""

Found:
Application.EnableEvents = True

End Sub


Note that using 1 in a VLOOKUP just returns the value that you were matching - in other words, it
does nothing useful. Unless you didn't want to use VLOOKUP, but use INDEX or some other
function....

HTH,
Bernie
MS Excel MVP


"Steven" wrote in message
...
I was give this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const sCell as string = "$A$1"
Const sListRange as string = "$D$1:$D$3"

Application.EnableEvents = False
With Range(sCell)
If Target.Address = sCell Then
.Value = Application.WorksheetFunction.VLookup( _
.Value, Range(sListRange), 1, False)
End If
End With
Application.EnableEvents = True
End Sub

It works fine except instead of = "$A$1" I want it to to be the ActiveCell
and instead of = "$D$1:$D$3" I want it to be an already existing named range
"Categories"

And if a match is not found then to make the current cell blank.

Thank you for your help.

Steven

VLookup in Code
 

That is really nice. Thank you.

One issue: What I am using it for is so if I want someone to type in for
example:
"NASD Fees" that they will not be able to type in Nasd Fees or NASD FEES or
any variation of the case. One thing. It works if the cell does not have a
value but if it does already have a value then no matter what is typed in it
will always revert back to the value when you entered the cell. ie You
cannot change a cell if it already has a value.

Is there a way to change this so the cell does not have to be blank to start.

Thank you for your help.

Steven

"Bernie Deitrick" wrote:

Steven,

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo NotFound:
With Target
.Value = Application.WorksheetFunction.VLookup( _
.Value, Range("Categories"), 2, False)
End With
GoTo Found:
NotFound:
Target.Value = ""

Found:
Application.EnableEvents = True

End Sub


Note that using 1 in a VLOOKUP just returns the value that you were matching - in other words, it
does nothing useful. Unless you didn't want to use VLOOKUP, but use INDEX or some other
function....

HTH,
Bernie
MS Excel MVP


"Steven" wrote in message
...
I was give this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Const sCell as string = "$A$1"
Const sListRange as string = "$D$1:$D$3"

Application.EnableEvents = False
With Range(sCell)
If Target.Address = sCell Then
.Value = Application.WorksheetFunction.VLookup( _
.Value, Range(sListRange), 1, False)
End If
End With
Application.EnableEvents = True
End Sub

It works fine except instead of = "$A$1" I want it to to be the ActiveCell
and instead of = "$D$1:$D$3" I want it to be an already existing named range
"Categories"

And if a match is not found then to make the current cell blank.

Thank you for your help.

Steven