VBA Lookup with Find method

[Oscar W]

Hi all,

I want to do a lookup in VBA to find the appropriate region for a
given country. They way I want to do is to loop through the country
column for all rows in my database sheet. For each country, find this
country on my Settings sheet, offset this cell by one column to get
the region. Finally paste the region one cell to the right of the
country cell in the database.

As I potentially could have the country names in several times on the
settings sheet, I only want to search in a specific range (i.e. where
I have countries in column 1 and regions in column 2).

I can't get the find method to work for only a specific range. What am
I doing wrong? (I have tried to include the settings sheet etc...)

The code looks like this:

For Count = 1 To LastRow

Country = WorkRange.Offset(Count, 0).Value

Set rTemp = LookupRange.Find(What:=Country, After:=[A1],
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

[more code]

Next count


Many thanks,
Oscar

 

[Joel]

I suspect your problem is with the [A1}. try removing and see if this is the
problem. the A1 and the Lookuprange need to specify the same worksheet

set LookupRange = sheets("sheet3).Coluns("A:A")

Set rTemp = LookupRange.Find( _
What:=Country, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If you need After then use Sheets("Sheet3").Range("A1")

 

[Oscar W]

Thanks a lot!