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

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

--
Message posted from http://www.ExcelForum.com

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

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)

"JC10001 " wrote in message
...
Hi Guys,

This is my first post. I've been reading the forums for a while
though. I find them helpful from time to time. I'm trying to teach
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 and
return the row number where the value is stored. But if the value was
not found I want to throw up a msgbox saying that the value wasn't
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 statement
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 unsuccessful
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 like
simple stuff like this is the hardest stuff to find.

Thanks.


---
Message posted from http://www.ExcelForum.com/

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

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

-----Original Message-----
Hi Guys,

This is my first post. I've been reading the forums for
a while
though. I find them helpful from time to time. I'm
trying to teach
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 and
return the row number where the value is stored. But if
the value was
not found I want to throw up a msgbox saying that the
value wasn't
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 statement
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
unsuccessful
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 like
simple stuff like this is the hardest stuff to find.

Thanks.


---
Message posted from http://www.ExcelForum.com/

.

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

Trev, your solution worked.

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

--
Message posted from http://www.ExcelForum.com

Question about what the Find function returns if the item wasn'tfound
 

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



"JC10001 <" wrote:

Hi Guys,

This is my first post. I've been reading the forums for a while
though. I find them helpful from time to time. I'm trying to teach
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 and
return the row number where the value is stored. But if the value was
not found I want to throw up a msgbox saying that the value wasn't
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 statement
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 unsuccessful
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 like
simple stuff like this is the hardest stuff to find.

Thanks.

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson