Macro help - search and if found, do

[cdev19]

Hi all

I am very stuck. Here's what I am trying to do:

- Sheet1 Cell D4 has a drop down list containing names.
- I need a macro to search through Sheet2 column D for that name
- IF found, then take some action (I have written code for this)
- ELSE (ie. if not found) do nothing.

I am a VBA beginner and can usually rely on searching this site to find something similar to what I require and tweak it to my needs. However, on this occasion I have searched and searched for an answer, but I always end up with code that LOOPS through all rows looking for the search string, and takes action for each row found. I don't know how to "remove" the LOOPS and just do it once, it always seems to ruin the code.

I just need the macro to find one instance in column D in order to take some action, no looping.

Any help would be greatly appreciated!

 

[Makrini]

Sub findit()
   Dim Cell As Range
    Set Cell = Sheets("Sheet2").Range("D:D").Find("ab")
    If Not Cell Is Nothing Then MsgBox (Cell.Address)
       
End Sub

 

[matthew230]

or

<code>
Sub findvalue()
If Application.WorksheetFunction.CountIf(sheets("sheet2").Range("d:d"), sheets("sheet1").range("d4").value) >= 1 Then

MsgBox "found"
Else
MsgBox "not found"
End If

End Sub
<code>

 

[cdev19]

Makrini and matthew230 thank you both very much!

Works perfectly and replaces lines and lines of buggy code.