VBA vlookup reference in different sheet

Question

In Excel 2007  I am looping through the values of column 4 in Sheet 2  Still in Sheet 2  I want to output the result of my vlookup formula into column 5  The vlookup formula needs to refer to Sheet 1 where the reference columns are  In order to do so I have the following formula  Range  E2     Application WorksheetFunction VLookup Range  D2                Worksheets  Sheet1   Range  A1 C65536    1  False    Problem  it returns error code 1004  I read that it was because I needed to Select Sheet 1 before running the formulas such as   ThisWorkbook Worksheets  Sheet1   Select   But then the searched value Range   D2   doesn t belong to Sheet 1 and it still return code 1004 after having brought Sheet 1 into view   What is the correct way to refer to a different sheet in this case

User · Answer

Your code work fine  provided the value in Sheet2 D2 exists in Sheet1 A A   If it does not then error 1004 is raised   To handle this case  try  Sub Demo       Dim MyStringVar1 As Variant     On Error Resume Next     MyStringVar1   Application WorksheetFunction VLookup Range  D2            Worksheets  Sheet1   Range  A C    1  False      On Error GoTo 0     If IsEmpty MyStringVar1  Then         MsgBox  Value not found       End If      Range  E2     MyStringVar1  End Sub

User · Answer

try this   Dim ws as Worksheet  Set ws   Thisworkbook Sheets  Sheet2    With ws      Range  E2   Formula     VLOOKUP D2 Sheet1  A  C 1 0   End With  End Sub   This just the simplified version of what you want  No need to use Application if you will just output the answer in the Range  E2     If you want to stick with your logic  declare the variables  See below for example   Sub Test    Dim rng As Range Dim ws1  ws2 As Worksheet Dim MyStringVar1 As String  Set ws1   ThisWorkbook Sheets  Sheet1   Set ws2   ThisWorkbook Sheets  Sheet2   Set rng   ws2 Range  D2    With ws2     On Error Resume Next  add this because if value is not found  vlookup fails  you get 1004     MyStringVar1   Application WorksheetFunction VLookup rng  ws1 Range  A1 C65536   Value  1  False      On Error GoTo 0     If MyStringVar1      Then MsgBox  Item not found  Else MsgBox MyStringVar1 End With  End Sub   Hope this get s you started

User · Answer

The answer your question  the correct way to refer to a different sheet is by appropriately qualifying each Range you use  Please read this explanation and its conclusion  which I guess will give essential information   The error you are getting is likely due to the sought-for value Sheet2 D2 not being found in the searched range Sheet1 A1 A65536  This may stem from two cases    The value is actually not present  pointed out by chris nielsen   You are searching the wrong Range  If the ActiveSheet is Sheet1  then using Range  D2   without qualifying it will be searching for Sheet1 D2  and it will throw the same error even if the sought-for value is present in the correct Range  Code accounting for this  and items below  follows   Sub srch       Dim ws1 As Worksheet  ws2 As Worksheet     Dim srchres As Variant      Set ws1   Worksheets  Sheet1       Set ws2   Worksheets  Sheet2        On Error Resume Next     srchres   Application WorksheetFunction VLookup ws2 Range  D2    ws1 Range  A1 C65536    1  False      On Error GoTo 0     If  IsEmpty srchres   Then       ws2 Range  E2   Formula   CVErr xlErrNA    Use whatever you want     Else       ws2 Range  E2   Value   srchres     End If End Sub    I will point out a few additional notable points    Catching the error as done by chris nielsen is a good practice  probably mandatory if using Application WorksheetFunction VLookup  although it will not suitably handle case 2 above   This catching is actually performed by the function VLOOKUP as entered in a cell  and  if the sought-for value is not found  the result of the error is presented as  N A in the result   That is why the first soluton by L42 does not need any extra error handling  it is taken care by  VLOOKUP      Using  VLOOKUP    is fundamentally different from Application WorksheetFunction VLookup  the first leaves a formula  whose result may change if the cells referenced change  the second writes a fixed value  Both solutions by L42 qualify Ranges suitably  You are searching the first column of the range  and returning the value in that same column  Other functions are available for that  although yours works fine

User · Answer

It s been many functions  macros and objects since I posted this question  The way I handled it  which is mentioned in one of the answers here  is by creating a string function that handles the errors that get generate by the vlookup function  and returns either nothing or the vlookup result if any    Function fsVlookup ByVal pSearch As Range  ByVal pMatrix As Range  ByVal pMatColNum As Integer  As String     Dim s As String     On Error Resume Next     s   Application WorksheetFunction VLookup pSearch  pMatrix  pMatColNum  False      If IsError s  Then         fsVlookup          Else         fsVlookup   s     End If End Function   One could argue about the position of the error handling or by shortening this code  but it works in all cases for me  and as they say   if it ain t broke  don t try and fix it

[vba] VBA vlookup reference in different sheet

Examples related to vba

Examples related to excel