Find a match in a list

[jlclyde]

When in coding for a macro I am not sure how to look at a value from
an input box (s1) and compare it to a list for possible matches.
(Range("A1:A10") I know how to get it there once this is done, but
for some reason this is kicking my butt.
Thanks for the help,
Jay

 

[Nick Hodge]

Jay

You can do this by setting a range variable, like so

Sub FindData()
Dim WhatToFind As String
Dim fnd As Range
WhatToFind = InputBox("EnterData")
Set fnd = Range("A1:A25").Find(WhatToFind)
If Not fnd Is Nothing Then
MsgBox "I found " & WhatToFind & " at " & fnd.Address
Else
MsgBox "I found nowt"
End If
End Sub

And then testing if it is not 'nothing' after 'setting' it

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk