lookups in other worksheets....

[Steve Lim]

RE:lookups in other worksheets....

I have two worksheets. One with textures and one with models.
I want to know how many models a texture is used in.

So I have libraryfile, texturefile in the TEXTURES worksheet and
texture, modelfile in the MODELS worksheet.

I've been toying with find.row, but to see if it is really there. If
it's not, I'll get N/A..

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim myOtherRng As Range
Dim myCell As Range
Dim res As Variant

With Worksheets("othersheet")
Set myOtherRng = .Range("a1:G" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

With ActiveSheet
Set myCell = .Range("a3") 'some cell???
res = Application.VLookup(myCell.Value, myOtherRng, 3, 0)
If IsError(res) Then
'same as n/a
'what to do?
Else
myCell.Offset(0, 1).Value = res
End If
End With

End Sub