Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use data from an external sheet.
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").Fin d(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use data from an external sheet.
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. -----Original Message----- 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").Fin d (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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use data from an external sheet.
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").Fin d (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 "Pete McCosh" wrote in message ... 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. -----Original Message----- 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").Fin d (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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
updating external data sheet causes loss of data on linked pages | Excel Worksheet Functions | |||
Adding a Comment-Column to an external data-sheet | Excel Worksheet Functions | |||
Update External Data source from Excel sheet | Excel Discussion (Misc queries) | |||
adding new columns sheet with external data | New Users to Excel | |||
refresh external data on a protected sheet | Excel Discussion (Misc queries) |