Excel 2010 Vlookup Problem

[Jaye7]

I am having trouble with excel 2010.

When I want to do a vlookup from one workbook to another it refuses to accept the different workbook as the source location.

I.e in excel 2000 the following would be the vlookup

ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1], '[TEMPLATE1.xls]Sheet1'!C1, 1, FALSE)"

whereas in excel 2010 it will not accept the [TEMPLATE1.xls] the code ends up as a vlookup within the workbook that you type the actual formula.

Can anyone shed sone light on this as it is very annoying???

 

[P.Holko]

I'm not sure I can help You but try to add a full path to the workbook or just force Excel to open it.

 

[Jonmo1]

Works fine for me.

My guess is that TEMPLATE1.xls is the currently active workbook at the time the code runs.
So excel says to itself...
Hmm, This formula is referring to it's own workbook, so I don't need the bookname in there, so I'll just remove it.

To test for sure, add this line before the line entering the formula

msgbox ActiveWorkbook.Name

 

[Jaye7]

I am manually typing the info into the cell of the spreadsheet that I want to vlookup therefore it is the actual workbook i.e. i type in the cell = vlookup(

then i click on the cell that I want to lookup followed by , then I click on the column of the workbook that I want to lookup, this is where it refuses to allow me to lookup within another workbook it always reverts back to the workbook that I am trying to enter the vlookup into.

As stated it works fine in excel 2000 version so I aren't making errors when doing the vlookup, it just refuses to let me vlookup within a different workbook in 2010.

 

[Jonmo1]

Perhaps both books (book with vba code, and Template1.xls) are NOT opened within the same instance of Excel.

If you first open the book containing your vba code
Then from within that book, go to file - open - and open the template1.xls file
Does it work then?

 

[Jaye7]

They are both open in the same session, for some reason the workbook that I am trying to find the data within is cancelling out my vlookup, which it didn't do in previous versions of excel.

It is taking control of the vlookup and the final vlookup formula is ending up in the wrong workbook, possibly because I have multiple forms and win timers running which is creating an error.

I tried it on 2 new workbooks and it worked fine.

Thanks for your help everyone.

 

[Jonmo1]

It is taking control of the vlookup and the final vlookup formula is ending up in the wrong workbook, possibly because I have multiple forms and win timers running which is creating an error.

The plot thickens...

So not only is the formula being adjusted to not include the workbook reference..
But the formula is also ending up in the wrong workbook.

This boils down to the ActiveWorkbook (at the time the code runs) is not the book you think it is.

It is best to avoid these kinds of problems by not depending on which book/page/range is currently active.
Always explicitly reference the appropriate book/sheet/range

Example

Sub test()
Dim MyBook As Workbook, MySheet As Worksheet, MyRange As Range
Set MyBook = Workbooks("BookName.xls") 'Name of book to put the formula in
Set MySheet = MyBook.Sheets("Sheet2") 'Sheet to put the formula in
Set MyRange = MySheet.Range("B1") 'Cell to put the formula in
 
MyRange.FormulaR1C1 = _
        "=VLOOKUP(RC[-1], '[TEMPLATE1.xls]Sheet1'!C1, 1, FALSE)"
End Sub

 

[Jaye7]

Thanks for your time and help Jonmo1, much appreciated.

I have seen other scripts that you have written for other people and have used several parts of the scripts to suit my various needs from time to time.

Definitely an MVP.

 

[Jonmo1]

Glad to help, thanks for the feedback.

So I assume then that the problem is resolved?