search column for a specific piece of data using vba
 

using vba in excel 2000, wish to search a column for specific data and return
that value to another cell.
can do this using vlookup but wish to use vba

search column for a specific piece of data using vba
 

Have a look at the Find Method in help.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"dave91" wrote in message
...
using vba in excel 2000, wish to search a column for specific data and
return
that value to another cell.
can do this using vlookup but wish to use vba

search column for a specific piece of data using vba
 

I'm not quite sure why you'd want to search a column for a value and return that
value (you already have that value--just use it!).

But you could do something like

dim res as variant
dim myVal as variant
dim myRng as range

with activesheet
set myrng = .range("a:a") 'single column
end with

myVal = "asdf"

res = application.match(myval,myrng,0)
if iserror(res) then
'not found in that column
else
msgbox myrng(res).offset(0,1).value
end if

..offset(0,1) means that I want to bring back the value from one column to the
right of the range.

==========
If you want to use =vlookup()

dim res as variant
dim myRng as range
dim myVal as variant

with activesheet
set myrng = .range("a:c") 'three columns
end with

myVal = "asdf"

res = application.vlookup(myval,myrng,2,false)
if iserror(res) then
'not found in that column
else
msgbox res
end if




dave91 wrote:

using vba in excel 2000, wish to search a column for specific data and return
that value to another cell.
can do this using vlookup but wish to use vba

--

Dave Peterson