Vlookup

Catalin.B · #1 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 · #2 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

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