Why the DataValidation Code not working?

[Soniya]

Hi All,

to my pre question I got the following code:

What I intend to do is in my sheet2 range A1,a1.end
(xldown) is named as "MemberList" and in my sheet1 B2 i
have a data validation type List(Memberlist)

if i type in sheet1 A2 the first few letters the data
validation in B2 should list only names starting those
typed letters in A2 since my list is so long browsing
down and selecting makes this way easy.

all my "MemberList" data are in Ucase

any idea why the following code do not work?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cInput As Long, cValues As Long
Dim i As Long, j As Long
Dim vValue
Const dvCell As String = "$B$2"
Const selectCell As String = "$A$2"
Const listSheet As String = "Sheet2"

Application.EnableEvents = False
If Target.Address = selectCell Then
Worksheets("Sheet2").Columns(3).ClearContents
cInput = Len(Target.Value)
cValues = Worksheets(listSheet).Range
("MemberList").Cells(Rows.Count, _
1).End(xlUp).Row
j = 1
For i = 1 To cValues
vValue = Worksheets(listSheet).Range
("MemberList").Cells(i, _
1).Value
If UCase(Left(vValue, cInput)) = UCase
(Target.Value) Then
Worksheets(listSheet).Cells(j, "C").Value
= vValue
j = j + 1
End If
Next i
End If

If j > 1 Then
ThisWorkbook.Names.Add Name:="shortRange",
RefersTo:="=" & listSheet _
& "!$C$1:$C$" & j - 1
With Range(dvCell).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=shortRange"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range(dvCell).Value = Worksheets(listSheet).Range
("N1").Value
Range(dvCell).Select
End If

Application.EnableEvents = True

End Sub


TIA
Soniya