VBA Vlookup with a complicated path...

[romainaccor]

Dear all,

Im trying to use the vlookup function in my code...and Im struggling with the path of the folder I want to look up in...

Const cPATHBF = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\[Incomestatibis.xls]Basic Fees IBIS'!$A$4:&AE&100"

RadressBF = Application.WorksheetFunction.VLookup(Cells(C_look, R_look), cPATHBF, " & Mois & ", False)

It displays the following message : Unable to get the Vlookup property of the worksheet function class.

Would someone know how to fix this code? Im guessing its from the ",' syntax...
Thanks guys!

[p45cal]

At first glance the bit that would worry me is
$A$4:&AE&100
and whether it should be:
$A$4:$AE$100

The next part is the vlookup; where it expects a column number, you're giving it a string: " & Mois & ".
My guess is that Mois is a (month) number so you could lose everything but Mois leaving:
RadressBF = Application.WorksheetFunction.VLookup(Cells(C_look, R_look), cPATHBF,Mois, False)

[romainaccor]

It still display the same message.. Do u think the syntax of the path to the server is correct?


Sub test()
Dim Month As String
Dim Mois As Double
Month = Sheets("date").Range("b3").Value
Dim R_look As Double
Dim C_look As Double
If Month = "01_January" Then Mois = "19"
If Month = "02_February" Then Mois = "20"
If Month = "03_March" Then Mois = "21"
If Month = "04_April" Then Mois = "22"
If Month = "05_May" Then Mois = "23"
If Month = "06_June" Then Mois = "24"
If Month = "07_July" Then Mois = "25"
If Month = "08_August" Then Mois = "26"
If Month = "09_September" Then Mois = "27"
If Month = "10_October" Then Mois = "28"
If Month = "11_November" Then Mois = "29"
If Month = "12_December" Then Mois = "30"
Dim RadressBF As Range
Dim MaligBF As Double
Dim RadressIS As Range
Dim MaligIS As Variant
Dim RadressTM As Range
Dim MaligTM As Variant
Dim Range1 As Range

Const cPATHBF = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\[Incomestatibis.xls] _
Basic Fees IBIS'!$A$4:$AE$100"



For R_look = 40 To 40
For C_look = 6 To 700
Set Range1 = Worksheets("YTD").Cells(C_look, R_look)
If Range("io" & C_look).Value Like "*IBIS*" Then


Set RadressBF = Application.WorksheetFunction.VLookup(Cells(C_look, R_look - 37), _
cPATHBF, Mois, False)
MaligBF = Range(RadressBF).Row
End If
Next
Next

[p45cal]

Goodness! If I don't put you off coding with what follows, I don't know what will!

There's some work to be done here..
I'll go through it.

Dim Mois As Double The above line declares Mois to be a full floating point decimal variable. It doesn't need to be - it's only ever going to be a whole number, so declare it as an integer or as a long:
Dim Mois as Long Dim R_look As Double
Dim C_look As Double The two lines above have been declared as Double - again it looks likely from lower down that they too will only ever be whole numbers because they refer to sheet columns and rows. In pre-Excel2007 rows can be as may as 65000+ while in Excel 2007, rows can be as many as a million. Integer data types only go up to 32000+ in Excel, so the Long data type suits well here:
Dim R_look As Long
Dim C_look As Long Also, regarding the above two lines: it makes the readability of the code easier if variables which refer to rows use the letter R in their names and variables which refer to columns use the letter C in their names. Here this is reversed. It's OK, just need to remember that.

If Month = "01_January" Then Mois = "19"
If Month = "02_February" Then Mois = "20"
If Month = "03_March" Then Mois = "21"
If Month = "04_April" Then Mois = "22"
If Month = "05_May" Then Mois = "23"
If Month = "06_June" Then Mois = "24"
If Month = "07_July" Then Mois = "25"
If Month = "08_August" Then Mois = "26"
If Month = "09_September" Then Mois = "27"
If Month = "10_October" Then Mois = "28"
If Month = "11_November" Then Mois = "29"
If Month = "12_December" Then Mois = "30"
The 12 lines above will try to assign a string to Mois (Mois = "25", for example), which you've already declared should be a number. Excel handles this OK but the quotation marks are unnecessary; remove them:
If Month = "01_January" Then Mois = 19
If Month = "02_February" Then Mois = 20 etc.

Const cPATHBF = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\ _
Basic Fees IBIS'!$A$4:$AE$100" The above line looks all right, but it depends on the server\folder structure for your network. I'll leave this until I'm sure everything else is right.

Set Range1 = Worksheets("YTD").Cells(C_look, R_look)
With the above line, you set Range1 to a different range for every iteration of the loop; nearly 700 times.. but you never use it within the loop! What it does imply however, is that C_look and R_look are used to refer to cells on a sheet called 'YTD'. I realise that this may not be the only sheet they're used in.

If Range("io" & C_look).Value Like "*IBIS*" Then
The line above doesn't qualify the sheet, so it uses the active sheet (unless this code is in a sheet's code module?). It would be safer to use:
If Worksheets("YTD").Range("io" & C_look).Value Like "*IBIS*" Then (assuming that YTD is the sheet that you want it to look at)

Set RadressBF = Application.WorksheetFunction.VLookup(Cells(C_look, R_look - 37), cPATHBF, Mois, False) There are three problems with the above line:
1. You've declared RadressBF as a range, but vlookup only returns a value. Consider using Find instead (VBA's Find, not application.worksheetfunction.find)
2. Cells(C_look, R_look - 37) is not qualified. Use:Worksheets("your sheet name here").Cells(C_look, R_look - 37)
3. Looking at the contents of cPATHBF you should change cPATHBF within the vlookup statement to Range(cPATHBF)

By the way, only for the purposes of checking the validity of cPATHBF (because you could be using it in the Find statement) you can use two lines like:
zz = Application.WorksheetFunction.VLookup(Worksheets("the sheet name here").Cells(C_look, R_look - 37), _
Range(cPATHBF), Mois, False)
msgbox zz
(VLookup will still error if Cells(C_look, R_look - 37) isn't found in first column of Range(cPATHBF)).

Ideas on how you might use Find:
Set RadressBF = Range(cPATHBF).Columns(1).Find(Cells(C_look, R_look - 37), LookIn:=xlValues, lookat:=xlWhole)
If Not RadressBF Is Nothing Then
MaligBF = RadressBF.Row
zz = RadressBF.Offset(, Mois - 1)
End If

Find does return a range, but that range is the cell in the first column of cPATHBF rather than the result that vlookup gave. The [I]zz in the above code should return the same value that vlookup would have. If Find can't find anything it returns Nothing. Check out Find more thoroughly as the arguments I've used may not suit you.

[romainaccor]

Wooo , I just read your answer, havent tried it yet, but whether it works or not, Is not the big deal! Just a thousand thank you for what you ve done! It is going to be thanks to people like you if one day I become good at vba coding !
Im gonna try the code now and let you know!!! And thanks again for explaining the steps...which is actually more important for me that just droppin the code...

[romainaccor]

Hello again
Ok so We are almost done... The code works but only when the file "Income stat Ibis" is open, and of course I need it to work with this file closed... When its closed, it highlights this line :

Set RadressBF = Range(cPATHBFIB).Find(Cells(C_look, R_look - 37), LookIn:=xlValues, lookat:=xlWhole)

and it displays the following error message : Method range of object_global failed

Do you see where it could be from...

Sub test()
Dim Month As String
Dim Mois As String
Month = Sheets("date").Range("b3").Value
Dim R_look As Long
Dim C_look As Long
If Month = "01_January" Then Mois = "s"
If Month = "02_February" Then Mois = "t"
If Month = "03_March" Then Mois = "u"
If Month = "04_April" Then Mois = "v"
If Month = "05_May" Then Mois = "w"
If Month = "06_June" Then Mois = "x"
If Month = "07_July" Then Mois = "y"
If Month = "08_August" Then Mois = "z"
If Month = "09_September" Then Mois = "aa"
If Month = "10_October" Then Mois = "ab"
If Month = "11_November" Then Mois = "ac"
If Month = "12_December" Then Mois = "ad"


Dim RadressBF As Range
Dim MaligBF As Double
Dim RadressIF As Range
Dim MaligIF As Double
Dim RadressTM As Range
Dim MaligTM As Double
Dim RangeBF As Range
Dim RangeIF As Range
Dim RangeTM As Range

Const cPATHBFIB = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\[Incomestatibis.xls] _
Basic Fees IBIS'!$A$4:$A$100"
Const cPATHIFIB = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\[Incomestatibis.xls] _
Incent Fees IBIS'!$A$4:$A$100"
Const cPATHTMIB = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\[Incomestatibis.xls] _
Trade Mark Ibis'!$A$4:$A$100"


For R_look = 40 To 40
For C_look = 6 To 700
If Worksheets("YTD").Range("io" & C_look).Value Like "*IBIS*" Then

Set RadressBF = Range(cPATHBFIB).Find(Cells(C_look, R_look - 37), LookIn:=xlValues, lookat:=xlWhole)
If Not RadressBF Is Nothing Then
MaligBF = RadressBF.Row

[Bob Phillips]

Simplest thing is to add a workbook open before you process the file.

[romainaccor]

I would do it but this is just a sample of my code..and I would have to open more than 50 excel files if had to process this way...So I really need to find the correct syntax to make it work without opening them before...

[Bob Phillips]

If the code is properly structured, the 50 files open would all be in a single called procvedure, so changing the code would not be onerous.

[p45cal]

I'm pretty sure that for any vba to access data in another file, that file needs to be open.
Vlookup in a formula on a sheet can get data from a closed workbook, but not code.
Are we really talking 50 files here or 50 sheets?

[Bob Phillips]

...
Vlookup in a formula on a sheet can get data from a closed workbook, but not code.

Execute Excel4Macro would do so.

[CBrine]

It is possible to extract data from a closed excel workbook, using the workbook as an ADO data source. I haven't looked at this post in detail, so I will leave it to you to figure out if this is a case where you would want to use it. Here's the code.



Private Sub CommandButton1_Click()
Dim Folder As String, File As String, stCon As String, stSQLNumberRows
Dim stRows As String, stSQL As String, count As Long
Dim cnt As ADODB.Connection, rst As ADODB.Recordset
stSQLNumberRows = 10
Folder = "C:\Test"
File = "Test.xls"
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
stCon = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & Folder & "\" & File
cnt.Open stCon
Set rst = cnt.Execute("[A1:A100]") 'Can also use a named range for a worksheet other then index 1.
ActiveSheet.Range("A65535").End(xlUp).Offset(1, 0).CopyFromRecordset rst
Set rst = Nothing
Set cnt = Nothing
End Sub

[geekgirlau]

The simplest way to check that your path syntax is correct:

Open the workbook you want to link to
Create a blank new workbook
Type "=" into a blank cell
Navigate to the linking workbook and highlight the cells you want to link to
Press [Enter] to finish
Closed the linking workbookYour formula should now contain the full path to the now-closed workbook - copy this into your code.

[romainaccor]

Ok so, this code works fine, the workbooks open itself

Workbooks.Open ("\\s-eu-dxb01nws01\DPT (file://%5C%5Cs-eu-dxb01nws01%5CDPT) Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\IncomeStatIbis")

But this one doesnt work, and the path is correct, its the same one that I have when I use Geekgirlau procedure.

Const cPATHBFIB = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\ _
[Incomestatibis.xls] Basic Fees IBIS'!$A$4:$A$100"

It may be comes from a parenthesis issue or ' or " ...?

[p45cal]

Ok so, this code works fine, the workbooks open itself

Workbooks.Open ("\\s-eu-dxb01nws01\DPT (file://%5C%5Cs-eu-dxb01nws01%5CDPT) Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\IncomeStatIbis")

But this one doesnt work, and the path is correct, its the same one that I have when I use Geekgirlau procedure.

Const cPATHBFIB = "'\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\[Incomestatibis.xls]Basic Fees IBIS'!$A$4:$A$100"

It may be comes from a parenthesis issue or ' or " ...?
Well yes, you're asking it to open file, sheet and range, stick with just the path and file name (it might be an idea to keep the '.xls' , I'm not sure) then refer to the workbook, sheet and range in the Find/vlookup statements.
You didn't answer my query "Are we really talking 50 files here or 50 sheets?" so I'm not sure how best to structure it, however, use variables, eg.:
filePathandName = "\\s-eu-dxb01nws01\DPT Finance&Admin\AB FINACE SHARED FOLDER\B09 V2\Bud 2009\Incomestatibis.xls"
BFIB = "Basic Fees IBIS"
DataRangeAddr = "$A$4:$A$100" 'this seems always to be the same
Set wkbk = Workbooks.Open(filePathandName)
Set RadressBF = wkbk.Sheets(BFIB).Range(DataRangeAddr).Find(stufftofind, LookIn:=xlValues, lookat:=xlWhole)
If Not RadressBF Is Nothing Then MsgBox RadressBF.Address Else MsgBox "not found"
wkbk.Close False
Set wkbk = Nothing
(Adjust 'stufftofind' above.)

Also in your code it would be more robust to qualify
Cells(C_look, R_look - 37)
in
Set RadressBF = Range(cPATHBFIB).Find(Cells(C_look, R_look - 37), LookIn:=xlValues, lookat:=xlWhole)