The FileToOpen & "uk!c1:c5" should be built so that it looks like:
'C:\My Documents\excel\[book1.xls]UK'!C1:C5

Option Explicit
Sub testme()

Dim FileToOpen As Variant
Dim myFolderName As String
Dim myFileName As String
Dim LastBackSlashPos As Long
FileToOpen = Application.GetOpenFilename
If FileToOpen = False Then
'user hit cancel
Exit Sub '??
End If

LastBackSlashPos = InStrRev(FileToOpen, "\", -1, vbTextCompare)

If LastBackSlashPos = 0 Then
'no backslashes, what the heck happened???
Else
myFolderName = Left(FileToOpen, LastBackSlashPos)
myFileName = Mid(FileToOpen, LastBackSlashPos + 1)
End If

' shooting for: 'C:\My Documents\excel\[book1.xls]UK'!C1:C5
ActiveCell.FormulaR1C1 _
= "=VLOOKUP(RC[-9],'" & myFolderName & "[" & myFileName & "]" _
& "UK'!C1:C5" & " ,5,FALSE)"

End Sub

LuisE wrote:

I need to do a VLOOKUP in a close file which name I'm getting from a variable
named FileToOpen using GetOpenFilename.

My problem starts when I try to add the the Sheet and the range to the
formula. The sheet name is UK and it'll be looking up from Column A to Column
E.

Here is what I have (not working obviously)

ActiveCell.FormulaR1C1 = _
'"=VLOOKUP(RC[-9]," & "[" & FileToOpen & "]" & "UK!C1:C5" & " ,5,FALSE)"

Thanks in advance

--

Dave Peterson