1. try recording a macro with the filter on the country to get the items you
want and then maybe you can modify the vba  code to exactly suite your needs.

2. try recording a macro where you use find to actuallly find a country and then
use offet function to move the activecell to copy the contents

as i said i am not a VBA expert but this si what i would try..Crude but
recording and mdifying the macros have worked on innumerable occasions for me..

I am a real rookie in this arena and not sure how to record what
I want to do. However, after a lot of reading, I THINK I am a little closer,
but not sure. Below is the code. When I execute the code, I get the error
message Run-time error '13' -- Type-mismatch on line 4 below (Set myRange =
....).

Dim myWB As Excel.Workbook
Set myWB = GetObject("E:\AORP Project\PPA_200610.xls")
Dim myRange As Range
Set myRange = myWB.Sheets("October 06").Range("C5:C62")
txtTot = myWB.Application.WorksheetFunction.VLookup("Iowa", Range(myRange),
5, False)
Set myWB = Nothing

The State Names are in C5:62 (which I believe is the Range) and I want to get
the value in column  5 of the row  I found the State in.

Do you have any idea where I am going wrong?

I tested it, it works fine if you drop "myWB." from in front of
the "Application.WorksheetFunction..." statement. (Also if you have set
Option Explicit don't forget to declare txtTot as string.)

please help both of us as i am also stuck with this Procedure
in my case i had different sheet in a xls file as there are limitation of auto
filter . i want to developed one form
for searching  and displaying the corresponding value in the the form

the range for a Vlookup should be a block starting from the top of the index
column and including the target column.

For example if you want to vlookup a value in column  C and the value you want
to return is in column G (the fifth col starting with G) the range would be
something like "C5:G62"