PDA

View Full Version : Solved: VLookup In VBA



hobbiton73
01-08-2013, 11:07 AM
Hi, I wonder whether someone may be able to help me please.

I'm attempting to use the code below to allow user to use a VLookup via VBA, in addition to presenting the user with on screen instructions via autopopulation of cell text values.

Option Explicit
Public preValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range, res As Variant
Dim rCell As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim lr As Long

If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndNow
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.EnableCancelKey = xlDisabled
lr = lr


With Target
Select Case True

Case Not Intersect(Target, Range("J7:J400")) Is Nothing
Set Cell = Worksheets("Lists").Range("B2:C23")
res = Application.VLookup(Target, Cell, 2, False)
If IsError(res) Then
Range("K" & lr).Value2 = res
Else
Range("K" & lr).Value2 = res
End If

Case .Column = 9

If .Value2 = "P" Then
.Offset(, 1).Value2 = "Enter the Project Code"
.Offset(, 2).Value2 = "Enter the name of the Project"
End If

Case Else
End Select
End With

EndNow:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.EnableCancelKey = xlInterrupt
End Sub
To explain the code as little more:

I'm trying to use the following code to create a dependent drop down list. So the user will select a value in column I, this in turn will create a drop down menu in column J, with only the values pertinent to the value in column I. Then, with both values selected it will autopopulate the relevant text value in column K. The values for the drop down lists are taken from a sheet called 'Lists' where I have set up the 'Dependent Lists' named ranges.

Case Not Intersect(Target, Range("J7:J400")) Is Nothing
Set Cell = Worksheets("Lists").Range("B2:C23")
res = Application.VLookup(Target, Cell, 2, False)
If IsError(res) Then
Range("K" & lr).Value2 = res
Else
Range("K" & lr).Value2 = res
End If

The next piece of code, basically deals with the scenario where the value P is selected in column I. The dependent drop down lists are bypassed and the text values for column J and K instruct the user to enter the information manually.

Case .Column = 9

If .Value2 = "P" Then
.Offset(, 1).Value2 = "Enter the Project Code"
.Offset(, 2).Value2 = "Enter the name of the Project"
End If
The problem I have is that I can get the second piece of script working but not the first. I can select the values in column I and J without any problem, but I cannot return the value in column K. I've been working on this for days and I've tried all number of methods without success.

I just wondered whether someone may be able to look at this please and let me know where I'm going wrong.

Many thanks and kind regards

Chris

GarysStudent
01-08-2013, 12:24 PM
Do you see any change if you replace:

res = Application.VLookup(Target, Cell., 2, False)

with

res = Application.VLookup(Target, Cell.Value, 2, False)

hobbiton73
01-09-2013, 10:51 AM
Hi @GarysStudent, thank you very much for taking the time to reply to my post and I'm sorry I didn't get back to you sooner.

I did try the solution you kindly provided, but was unable to get this to work.

However, after working on this today, I've managed to get this to work by using the following:

If Not Intersect(Target, Range("J7:J400")) Is Nothing Then
Set Cell = Worksheets("Lists").Range("B2:C23")
res = Application.VLookup(Target, Cell, 2, False)
If IsError(res) Then
Range("K" & Target.Row).Value = "Enter the name of the project"
Else
Range("K" & Target.Row).Value = res
End If
End If

With Target
Select Case True
Case .Column = 9

If .Value2 = "P" Then
.Offset(, 1).Value2 = "Enter the Project Code"
.Offset(, 2).Value2 = "Enter the name of the project"
End If

Case Else
End Select
End With

Many thanks and kind regards