Vlookup

ibrahimaa · #1 12-21-2011, 01:20 AM

I am using Vlookup in one of xls files that users have it on their desktop to search for variable data located on our server that I update it monthly. The user file is about 1MB while the server file is about 6MB. Because of Vlookup, the user file expanded almost to 6.5MB to contain the server file.

According to our company internal policy, this size of the user file (6.5MB) is not recommended for different reasons. Is there any other formula or method that can provide the same function in different than Vlookup’s technique? I appreciate your support. Thank you.

Pecoflyer · #2 12-21-2011, 03:20 AM

Hi
difficult to say without seeing what you need to do.
Maybe post a sample sheet explaining what you are trying to achieve.

ibrahimaa · #3 12-21-2011, 03:23 AM

=IF($B6=7,VLOOKUP($F$5,'\\Server name & file bath\[File name.xlsx]July-Data'!$A$9:$R$7915,3,FALSE),

IF($C6=8,VLOOKUP($F$5,'\\Server name & file bath\[File name.xlsx]Aug-Data '!$A$9:$R$7915,5,FALSE),

IF($C6=9,VLOOKUP($F$5,'\\ Server name & file bath\[File name.xlsx]Sep-Data'!$A$9:$R$7915,7,FALSE), "Data is not Available yet")
) )

The server file contains 8 tabs; from May until Dec where each month data is in a separate tab.

So, if the user entered July in $B6 in the user file, the Vlookup will search in July tab of the server file.

I do not have any formulas in the server file. Thank you.

Catalin.B · #4 12-22-2011, 01:57 AM

You can use a macro to do this, and you will not have formulas in the user file, just results, which means a smaller size.
use :

Code:

Sub getvalues()
Dim Path  As String, Myrange As Range, ShName As Worksheet, Wb As Workbook

Path = ThisWorkbook.Path ' put your server path
Wb = Path & "\filename.xlsx"
Myrange = "A9:R7915"

Select Case ActiveSheet.Cells(6, "C")
Case 7
ShName = Sheets("July-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case 8
ShName = Sheets("Aug-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case 9
ShName = Sheets("Sep-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case Else
End Select
End Sub

Modify this code to fill a range with this formula result. There might be some errors, i did not have the time to test it, it's just to give you a clue.

ibrahimaa · #5 12-23-2011, 11:52 PM

Thanks a lot for your support. I have created a macro that I think I couldn’t save it because of the error that I have. The error message was “Object variable or with block variable not set:
And therefore, I have tried to insert set but still did not work. I do not know if I will face any other problem in the server bath, security option or any other area. Once again, I appreciate your support. Thank you.

Code:

Sub getvalues()
Dim Path As String, Myrange As Range, ShName As Worksheet, Wb As Workbook
Set MyObject = Sheets(1) which name should I have here?
Path = "\\aramco.com\data\ir\er&t\T&CD\ITD\crwrtd\JTS\JTS_Common\IAA\[Report-1c-Server.xlsx"
Wb = Path & "\filename.xlsx"
Myrange = "A9:R7915"
 
Select Case ActiveSheet.Cells(6, "C")
Case 7
ShName = Sheets("July-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case 8
ShName = Sheets("Aug-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case 9
ShName = Sheets("Sep-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case Else
End Select
End Sub

Pecoflyer · #6 12-24-2011, 09:14 AM

Ibrahimaa

it's a good idea to insert your code between code tags. It makes code easier to read and to copy for testing.
Edit your post, select Go Advanced, select the code and click the #icon. then save

ibrahimaa · #7 12-24-2011, 09:23 PM

The error message was “Object variable or with block variable not set:
And therefore, I have tried to insert set but still did not work. I do not know if I will face any other problem in the server bath, security option or any other area

Code:

Sub getvalues()
Dim Path As String, Myrange As Range, ShName As Worksheet, Wb As Workbook
Set MyObject = Sheets(1) which name should I have here?
Path = "\\aramco.com\data\ir\er&t\T&CD\ITD\crwrtd\JTS\JTS _Common\IAA\[Report-1c-Server.xlsx"
Wb = Path & "\filename.xlsx"
Myrange = "A9:R7915"
 
Select Case ActiveSheet.Cells(6, "C")
Case 7
ShName = Sheets("July-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case 8
ShName = Sheets("Aug-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case 9
ShName = Sheets("Sep-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(F5, Wb.ShName.Myrange, 5, False)
Case Else
End Select
End Sub

Catalin.B · #8 12-29-2011, 01:27 AM

try this, as i said, the code in the other post is a sample, not tested...(my mistake, i should not post such code)

Code:

Sub getvalues()
Dim Path, Myrange, ShName, Wb As String
Path = ThisWorkbook.Path 'enter your source file path
Wb = "Copy of intervale valori.xlsx" 'adjust as needed
Myrange = "A1:b16" 'change as needed
 Workbooks.Open Filename:=Path & "\" & Wb
 With ThisWorkbook.Sheets("Sheet1") 'change sheet1 to your sheet name
 .Activate
Select Case ThisWorkbook.Sheets("Sheet1").Cells(6, "C")
Case 7
ShName = "July-Data"
ActiveSheet.Cells(21, "C") = Application.VLookup(ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 2, False)
Case 8
ShName = Sheets("Aug-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
Case 9
ShName = Sheets("Sep-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
Case Else
End Select
End With
Workbooks(Wb).Close , savechanges = True
End Sub

you can change the red string: (ActiveSheet.Cells(9, "E")-this means that the search string is in cell E9)
Application.VLookup(ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
with : "Supermarket", or "Super*" (to search for a fragment of text)

ibrahimaa · #9 01-03-2012, 09:32 PM

It worked well. I highly appreciate your support. Thank you.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using Vlookup & IF together	thelauncher	Excel	5	08-25-2013 11:32 PM
Vlookup	Karen615	Excel	4	09-12-2011 02:30 PM
Can i do this with a VLookup?	foodstudent	Excel	1	01-21-2011 12:34 AM
Using IF & VLOOKUP together	junction	Excel	7	11-18-2010 05:15 AM
Help with VLOOKUP	sakhtar	Excel	2	07-24-2010 07:39 PM