Find something

[Looping through]

I need to come up with a way to copy a cell in certain tab and then goto
another tab in the same workbook and perform a search function. Paste the
copied cell info in the find field and find the match. After it is found
close out the find dialog box and delete the entire row that returned a match.

this is what I have so far.

ActiveCell.Offset(0, -10).Range("A1").Select
Selection.Copy
Sheets("Current Month").Select
Range("H10").Select
Cells.Find(What:="211989", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveCell.Select
Sheets("Master Log 2008").Select
ActiveCell.Offset(0, -1).Range("A1").Select

The "Cells.Find(What:="211989"," is what is killing me. I don't know exactly
what that number is going to be based on the inforperformed above the start
of this code.

Is there a way to get around this or make the "What:" some sort of wild card
based on the users clipboard info within their mouse?

Any suggestions
thanks
Peter

 

[Jim Thomlinson]

You don't want/need to copy the cell contents... Something more like this
will probably work better...

'**********************************
Dim rngToDelete As Range

Set rngToDelete = Sheets("Current Month").Cells.Find( _
What:=Sheets("????").Range("A1").Value, _
LookAt:=xlPart, LookIn:=xlFormulas, MatchCase:=False)

If rngToDelete Is Nothing Then
MsgBox "No Match"
Else
rngToDelete.EntireRow.Delete
End If
'*********************************

You will need to change the line...
Sheets("????").Range("A1").Value
adding the sheet name where you are getting the find value

 

[Bernie Deitrick]

Peter,

No need to copy - this will work with the 10 column offset - might be better if you select the cell
with the value directly and remove the Offset(0,-10) part...

Sub DeleteMatch()
Sheets("Current Month").Cells.Find( _
What:=ActiveCell.Offset(0, -10).Value, _
LookIn:=xlFormulas, _
LookAt:=xlPart).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[Looping through]

Thanks Bernie, This worked perfectly.

Peter,

No need to copy - this will work with the 10 column offset - might be better if you select the cell
with the value directly and remove the Offset(0,-10) part...

Sub DeleteMatch()
Sheets("Current Month").Cells.Find( _
What:=ActiveCell.Offset(0, -10).Value, _
LookIn:=xlFormulas, _
LookAt:=xlPart).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP