Find/Search macro

[LB79]

What is the easiest way to search using a list that constantly updates
For example, I have a list of names in cells A1:A10 (1 name per cell)
Not all cells will have a name all of the time eg one day A1:A7 wil
have names, the next day A1:A1 only will have a name. I want the macr
to look at these names then look for them in another worksheet an
clear the contents of that cell.
Does anyone know of a way to do this

 

[Bernie Deitrick]

LB,

If by Worksheet you mean another sheet in the same book:

Dim myCell As Range

For Each myCell In Range("A1", Range("A65536").End(xlUp))
Worksheets("Other worksheet").Cells.Replace myCell.Value, "", xlWhole
Next myCell

If by Worksheet you mean another book altogether:

Dim myCell As Range

For Each myCell In Range("A1", Range("A65536").End(xlUp))
Worksbooks("Other Workbook").Worksheets("Other worksheet"). _
Cells.Replace myCell.Value, "", xlWhole
Next myCell

HTH,
Bernie
MS Excel MVP

 

[jeff]

Hi,

Try this macro. It loops thru names in sheet1 colA, trys
to find each name in sheet2 range A1:Z500 and deletes it
if it finds it. (run by comandbutton #1)

jeff

Private Sub CommandButton1_Click()
GoFindem
End Sub

Sub GoFindem()
Dim r As Range
Dim s As Range
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = Worksheets("Sheet1").Range("A1:A" & lastrow)
For Each d In r
With Worksheets("Sheet2").Range("a1:z500")
Set c = .Find(d, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'c.Offset(0, 0).Value = "****"
c.ClearContents
Set c = .FindNext(c)
On Error Resume Next
Loop While Not c Is Nothing And c.Address <>
firstAddress
End If
End With
Next d
End Sub