#1
|
|||
|
|||
Vlookup
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. |
#2
|
||||
|
||||
Hi
difficult to say without seeing what you need to do. Maybe post a sample sheet explaining what you are trying to achieve.
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
=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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 Last edited by macropod; 01-03-2012 at 11:34 PM. Reason: Added code tags |
#6
|
||||
|
||||
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 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) |
#9
|
|||
|
|||
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 |