need help with autocomplete in excel

[niyrho]

I'm trying to build kind of an autocomplete macro for excel, but I'm new at this and have been just beating my head against the wall.

My idea was something like:

On selection change
If target cell is (sheet 1) a1 then
do untill onkey "enter" or selection change
onkey "any key"
vlookup(sheet 1 a1, sheet 2 a1-b100,2,false)
?
end

Trying to get it to, when cell a1 is selected it will use vlookup every time you press any letter or number key.
The "?" is cause I don't konw how to display the result. I would like it to show it the same way the normal autocomplete function does. If thats too tough just putting it in another cell will work.

My plan is to use this as an autocomplete function so my employees don't have to search through a list of 3000+ customers everytime thy're on a job.

Anyway, thats kinda what I want, just can't get the codes right. Never actualy use those ones before.

[westconn1]

to specify a range use like
sheets("sheet1").range("a1")
the default property for a range is value, so if target value is the same as the value of A1 would be equal
use the intersect function to find is target is within a specified range, or the address property of target and range

maybe the find function would be better than vlookup for what you want to do, as vlookup returns the value of some cell in the vlookup range, whereas you can set a range object with find then select that range

[niyrho]

What "find" function are you talking about? I couldn't find that in any of my books or in the help files.

[westconn1]

excel find method
menu > edit > find, also can be run by code

dim r as range
set r = range("a:a").find("sometext")  ' other parameters also can be used
r.select