Question about what the Find function returns if the item wasn't found

J

JC10001

Hi Guys,

This is my first post. I've been reading the forums for a whil
though. I find them helpful from time to time. I'm trying to teac
myself how to code VBA for Excel.

Anyway, I have a question I'm hoping someone can answer for me.

Basically, what I'm trying to do is search a column for a value an
return the row number where the value is stored. But if the value wa
not found I want to throw up a msgbox saying that the value wasn'
found.

Here is a code sample:

iRow = Columns("A").Find(sName).Row

if (condition where the value was not found) then
msxbox ("Name: '" & sName "' was not found.")
end if

What I need to know is the condition that will make the if statemen
true. I've tried using things like IsNull(iRow) but that did not work
Does the find function return a special value if it is unsuccessfu
and if so, what is it? If not, any suggestions on how to handle this?
I've been looking everywhere for the answer to this but it seems lik
simple stuff like this is the hardest stuff to find.

Thanks
 
B

Bob Phillips

Try this

on error resume next
oCell = Columns("A").Find(sName)
if not ocell is nothing Then
iRow = oCell.Row
else
msgbox "Name: '" & sName "' was not found."
end if

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Trev

Hi,
Unlaie Access, Excel doesn't return Null on ann empty cell
it returna a blank string.
Try something like

strSearch = "FRED"
for I = Toprow to bottomrow
If cells(I,1) =strSearch then
Msgbox "Found & strsearch & " on Row " & I '
Exit For
End if
Next
If I >bottomrow then
Msgbox "Not Found " strSearch
End if

Trev
 
J

JC10001

Trev, your solution worked.

Thank you both very much for the help. It's greatly appreciated
 
D

Dave Peterson

You could use .find, too.

Record a macro when you select column A and then do your find.

I got code like this:

Option Explicit
Sub Macro3()

Columns("A:A").Select
Selection.Find(What:="asdf", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

You could modify it like:

Option Explicit

Sub Macro3()
dim FoundCell as range

with columns("A:A")
set Foundcell = .Find(What:="asdf", After:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
msgbox "wasn't found"
else
msgbox foundcell.row
'application.goto foundcell, scroll:=true
end if

End Sub
 
Top