Vlookup between sheets

[Moonbeam111]

I am trying to use vlookup between sheets but I get an object required error on the third line (sres.select). Can anyone tell me how come so that I can fix this code?

celllookup = Range("H193")
sres = Application.VLookup(celllookup, Sheets("Sheet2").Range("H2:J15"), 1, False)
sres.Select

If Selection.Offset(0, 1) <= Sheets("Sheet1").Range("H198") Then
Range("H194") = Selection.Offset(0, 2)
End If

 

[Joe4]

You are looking up and returning a value, not a range (VLOOKUP formulas return values).
How can you select a value in VBA? That doesn't make sense.

 

[Moonbeam111]

Do you have any suggestions about how I can select this sres any other type of way? If I could get it selected, I think Id be ok from there. Because I can then use offset the selection to the rest of the values.

 

[Joe4]

Can you please explain (in plain English) exactly what it is you are trying to do, as it really doesn't make sense from your code?
Maybe show us an example?

Perhaps you are just trying to find a value in a range and select it?
If so, then you would just use the FIND function. VLOOKUP returns a string, not a range.

 

[Alex Blakenburg]

Also you have both MS365 and O2010 on your profile.
In principle you can do what you are trying to do using either XLookup or Index-Match both of which are able return an actual range as opposed to just its value (as is the case for a VLookup).
You want to avoid Select and Activate. It slows down the code and it makes it easy to lose track of what sheet you are on. eg you are trying to select a cell on a different sheet without activating it which you can't do. If you were to activate it then some of your later Range references would be pointing to the wrong sheet.

This is probably the closest to the way you were heading using the MS365 XLookup.

Sub LookupMod()

    Dim sht1 As Worksheet, sht2 As Worksheet
    Dim celllookup As Variant, Lookup_Rng As Range, Return_Rng As Range
    Dim sres As Range, sresVal As String
   
    Set sht1 = Worksheets("Sheet1")
    Set sht2 = Worksheets("Sheet2")
   
    With sht2
        Set Lookup_Rng = .Range("H2:H15")
        Set Return_Rng = .Range("H2:H15")
    End With
   
    celllookup = sht1.Range("H193")
    sresVal = Application.XLookup(celllookup, Lookup_Rng, Return_Rng, "NA")
    If sresVal <> "NA" Then
        Set sres = Application.XLookup(celllookup, Lookup_Rng, Return_Rng)
   
        If sres.Offset(0, 1) <= sht1.Range("H198") Then
            sht1.Range("H194") = sres.Offset(0, 2)
        End If
   
    Else
        MsgBox "Value was not found"
    End If
End Sub