Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to VLOOKUP as a Variable (range in another file)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to VLOOKUP as a Variable (range in another file)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range to VLOOKUP as a Variable (range in another file)
There are lots of ways to skin a cat <ouch!
In fact, if you were using a version of excel before xl2k, then instrrev wouldn't be available. You could loop backwards looking for a backslash. Dim iCtr as long for ictr = len(filetoopen) to 1 step -1 if mid(filetoopen,ictr,1) = "\" then 'found it myFoldername = left(filetoopen,ictr) myfilename = mid(filetoopen,ictr+1) exit for 'stop looking for more! end if next ictr ..... (Untested, uncompiled. Watch for typos.) LuisE wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLookup in VBA code with variable range | Excel Programming | |||
VLOOKUP variable range cell reference | Excel Worksheet Functions | |||
Vlookup using variable path name for range value | Excel Worksheet Functions | |||
VLOOKUP using a range variable | Excel Programming |