Reputation: 353
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?
Upvotes: 4
Views: 190002
Reputation: 353
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".
Upvotes: 2
Reputation: 15641
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).
Upvotes: 3
Reputation: 53166
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
Upvotes: 2
Reputation: 19737
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.
Upvotes: 7