Use data from an external sheet.

[Prasad Vanka]

Hi,

I am developing an application using Ms Excel 2002. I have a countries
worksheet which holds all the valid countries in one column. This
worksheet is part of my main workbook. The only purpose of this
worksheet is for validating input records that contain a country name.
So whenever I read an input record then I activate this sheet and
check whether the country is valid as follows:
If Not Worksheets(strCountriesWorksheet).Range("a:a").Find(strCountry,
LookIn:=xlValues) Is Nothing Then
IsValidCountry = True
End If

I want to know whether there is any better method. Can I load all the
countries from this sheet into some array (or any thing else) and
later use that to search for the country. If so can someone give me
some example code how to use array (or any thing else) for
searching/lookup.

Thanks in advance
Prasad Vanka

 

[Pete McCosh]

Prasad,

one suggestion would be to use Data Validation for the
input cell. Make your list of countires a named rangem
e.g. "CountryList", then set data validation for the data
entry cells to List>> "=CountryList"

I may have misunderstood the way you're getting your data
into the application however, in which case, my apologies.
Pete.

 

[Prasad Vanka]

Hi Pete,
You have misunderstood. CountryList has the list of countries in the
first column. My application reads through an input sheet (that comes
through an email). This input sheet has many columns one of which is
the country id. I have to validate this country id field by checking
whether it exists in the list in the CountryList sheet.
At the moment, for every record read from the input file, I am
activating the CountryList sheet and then checking as follows:
If Not Worksheets(strCountriesWorksheet).Range("a:a").Find
(strCountry, LookIn:=xlValues) Is Nothing Then
IsValidCountry = True
End If

My question was whether there is any better way than doing this.
Something like reading the CountryList sheet once and getting all the
values into an array and then use this array for validation.

Can someone help.
Thanks