Yes, Worksheest should be Worksheets

and in the formula, the address should be absolute instead of relative, so
any non-zero number (you chose -10) could be user or use True

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(True,True,xlR1C1,True) & ",11,FALSE)"

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hi Tom,

I fixed it. I changed
set rng = bk.Worksheest("Sheet1").Range("C1:I11") to Worksheets
and
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(-10,-10,xlR1C1,True) & ",11,FALSE)"

Thanks for your help!!
Aaron

"Tom Ogilvy" wrote:

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

set rng = bk.Worksheest("Sheet1").Range("C1:I11")
'Here I need it to select the file I started the Macro in.
ThisWorkbook.Activate
Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off
of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(0,0,xlR1C1,True) & ",11,FALSE)"

End sub

May work. You might have to put rng.address(0,0,xlR1C1,True) into a
string
and put in the single quotes.

--
Regards,
Tom Ogilvy

"Aaron" wrote in message
...
Hi Tom,
I can almost get this to work. Any Ideas? See below.

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

'Here I need it to select the file I started the Macro in.

Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup
off
of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10],'H:\MAT_MGMT\HP HDD and Tape Folder\Backlog
Report\[HP Backlog Report 01-06-05.xls]Sheet1'!C1:C11,11,FALSE)"

End sub

Thanks again!
Aaron
"Tom Ogilvy" wrote:

Dim fName as String, bk as Workbook
Dim rng as Range
fName = Application.GetOpenFileName()
if fName < "False" then
set bk = Workbooks.Open(fName)
End if


set rng = bk.worksheets(1).Cells.Find("ABCD")
if not rng is nothing then
msgbox "ABCD found at " & rng.Address(external:=True)
End if

See the Excel VBA help example for FindNext if you need to find
multiple
instances of "ABCD" (as an example).

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hello All,

I am trying to have a file do some lookups off of another file.
My
problem
is, I need the Macro to prompt me with a file browser and look at
the
file
that I choose and then do the lookups off that file. The format
in
the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron