Vlookup

ibrahimaa · #1 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 · #2 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)

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