Find something
 

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

Find something
 

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" wrote in message
...
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

Find something
 

Thanks Bernie, This worked perfectly.

"Bernie Deitrick" wrote:

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" wrote in message
...
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