Dear all,
I have two worksheets. One is the input, the other holds a list of 100s of names (ultimatley it will be two separate work books because the input workbook will be started from zero each year, but the list sheet will grow... but step after step.. as I assume that debuging is easier within the same workbook first and I don't have the skills and understanding to skip this step with confidence).
I know that I could copy/link all the names into the cells above my input cells and hide them, but I don't think that is a very clean way.
Googeling a solution I found this
http://www.mrexcel.com/forum/showthread.php?t=31107
for a text field and tried to adapt it to a cell. But for some reason I am not able to get it to run.
Can you please help me understand where I am wrong?
Any help is very much appreciated.Option Explicit Dim oRange As Range Dim iCharCount As Integer Dim strAuto As String Dim sTemp As String Private Sub Worksheet_Change(ByVal Target As Range) ' to which cell changes to react (this works fine) If Not Intersect(Target, Range("B2:C10000")) Is Nothing Then ' if intersect is nothing it would be out of range If Range("B10000").End(xlUp).Row = Range("C10000").End(xlUp).Row Then Range("F1").Formula = Range("B10000").End(xlUp).Row End If ' here starts the autocomplete Set oRange = Worksheets("list").Range("a2").End(xlUp).Offset(999, 0) 'select from A2 to A1000 oRange.Value = Target.Text strAuto = oRange.AutoComplete(Target.Text) If Len(strAuto) > 0 Then With Target sTemp = .Text .Text = strAuto .SelStart = Len(sTemp) .SelLength = Len(strAuto) End With End If MsgBox Target.Text oRange.ClearContents End If End Sub