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