Find a value in named range using VBA

[insanity82007]

How do I do this?

If the value exists in the named range I want to use an if statement to do something but I'm not sure how to find a value in the named range.

I tried this but it didn't work:

    For Each Value In Range("DropListCC")
        If Value = Sheet1.Range("e5").Value Then
            Exit For
        Else
            Sheet1.Range("e5").Value = ""
        End If
    Next

 

[TKB]

2 questions to start:

1. What is the value and what cell is it located in.

2. What do you want the IF statement to do?

 

[Trebor76]

Hi insanity82007,

Try this, which is much more efficient then looping through each cell in your named range:

Option Explicit
Sub Macro4()

    'http://www.mrexcel.com/forum/showthread.php?642219-Find-a-value-in-named-range-using-VBA
    
    Dim rngFoundCell As Range
    
    With Range("DropListCC")
        Set rngFoundCell = .Find(What:=Sheets("Sheet1").Range("E5").Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        If Not rngFoundCell Is Nothing Then
            'insanity82007, this is where you'd run code for the matching cell in your named range.
            'I've currently just returned the cell's address into a message box
            MsgBox rngFoundCell.Address
        Else
            MsgBox "There was no matching cell found in the ""DropListCC"" named range for the value in cell E5 of Sheet1."
        End If
    End With

End Sub

HTH

Robert