Lookup & clear a cell

Hi,

I've got a project that contains a list of items in a:a & their prices in b:b. I'm trying to be able to look up an existing product (d1) & clear it & it's corresponding price out of their lists. I've tried numerous things, and although I can find the data, I can't actually get the data cleared. Any ideas? Here's a simplified sample that at least finds the correct data.
Thanks!

Private Sub CommandButton1_Click()
Dim res As Variant
res = Application.VLookup(Range("D1").Value, Range("products"), 1, False)
If IsError(res) Then
MsgBox "not found"
Else
MsgBox res
End If
End Sub

Hi, bhsoundman,

VLookup will always find Values while Match will find the row where the data is located (if at all).

Dim res As Variant
res = Application.Match(Cells(1, 4).Value, Columns(1), 0)
If Not IsError(res) Then
MsgBox res
Else
MsgBox "not found"
End If
Ciao,
Holger

Thanks for the reply. How do I clear that cell now?

Hi, bhsoundman,

you´ve got the row number in res, just use Cells(res, 1) for the cell and either ClearContents to clear the Cell, set the value to vbNullString or delete the row if wanted (we could use EntireRow to user the cell address). Old line was

MsgBox res
new line should be one of the following

Cells(res, 1).ClearContents
Cells(res, 1).Value = vbNullString
Rows(res).Delete
Ciao,
Holger

It works perfectly! THANK YOU!!