Find and delete

[newmand2]

I am making a database in excel, and I have come across a problem.
want the user to be able to type a user ID in a cell and press a butto
labelled delete. I then want the macro to search the database workshee
for a cell containing EXACTLY the same as the input cell and delete th
row. Any ideas?

Thanks in advance!

Dav

 

[Tom Ogilvy]

Use the find method. Turn on the macro recorder and do Edit=>Find. Turn
off the recorder.

Now you can generalize the code to replace you hardcoded search term with a
reference to the cell where the ID will be entered.

 

[newmand2]

yes, but how do i do that? I know how to record the macro, its just
case of retrieving the input as a variable in the coding, and i trie
the edit => find thing, but for some reason u cant record it. Thank
agai

 

[Tom Ogilvy]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/30/2004 by OGILVTW
'

'
Cells.Find(What:="value", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
End Sub


recorded fine for me.

Then to make it more general as an example (search for a term on the
activesheet that is found on a worksheet named Sourcesheet in cell B9)

Dim rng as Range
Dim rng1 as Range

set rng1 = Worksheets("Sourcesheet").Range("B9").Value

set rng = Cells.Find(What:=rng1.Value, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

if not rng is nothing then
msgbox "found in " & rng.Address(external:=True)
else
msgbox "Not found"
End if