Find row number with VLookUp (VBA)

[PhilC999]

Hi all,

    Item = Val(ItemNo.Caption)
    Description = Application.WorksheetFunction.VLookup(Item, _
                    wParts.Range("$B$2:$B" & LastRW), 1, False)

I have the above code which successfully looks up a unique value in the range on the wParts worksheet. However, the row which the value appears on then has to be deleted. Is there a way to discover the row on which the value was found?

Phil

 

[Richard Schollar]

Hi Phil

Why don't you use the VBA Find() method to return a reference to the actual found cell or maybe Application.Match to locate the row instead of Vlookup?

 

[PhilC999]

Hi Richard,

Thanks for the quick reply. I've just had a quick search for both the methods you suggested but nothing seems immediately apparent. Would it be possible to for you to give a quick example of the FIND() method?

Phil

 

[Richard Schollar]

Sure:

Dim rFound As Range
With wParts.Range("$B$2:$B" & LastRW)
  Set rFound = .Find(What:=ItemNo.Caption, After:=.Range("A1"))
End With
'Test if value found:
If Not rFound Is Nothing Then 'if it's not nothing then it's something ie it was found
  rFound.EntireRow.Delete 'deletes the value and the entire row
End If

 

[PhilC999]

Hi Richard,

That works perfectly. Thank you very much!