hi, another thing regarding this macro module is that if the files to look up
for the values are different, how would I have to assign each file to the
macro?

I have tried out using input box to replace the file name(s) but the
directory will still remain, so is there any other way to do it? Please
advise........

Thanking in advance.


"Jac" wrote:

hi,

I have a macro coded as below:-

Sub LookupValues()

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'C:\Documents and Settings\Jac\Desktop\Book2.xls'
_myRange,2,0)"
Selection.AutoFill Destination:=Range("B1:B3")

End Sub

But this macro can only fill up the specific range with values which is
Range("B1:B3") but I wish that I could have the way that the macro can help
to fill up values for range with unknown length, which mean sometimes the
range could be longer or sometimes the length could be shorter.

I have tried out some methods but they seem like not working out as
expected. So someone please help to advise....

Thanking in advance.