Range to VLOOKUP as a Variable (range in another file)
Dave,
Thanks a lot, works like a charm
Actually I was hoping that you read the posting, I read your related ones
but couldn't find anything for the file as a variable.
Is it the only was? I was trying to get the path as a string out of the
GetFile... but I couldn't
Thanks again.
"Dave Peterson" wrote:
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
|