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.
[vba]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 [/vba]
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.
[vba] 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[/vba]
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.
[vba]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[/vba]
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
Reply With Quote
