Check the cell text match with Define Name

[trprasad78]

Hi all,

I need your help,

I had use macro to check country name in E2 Cell

If Range("E2") = "KSA" Then
    Range("Ai2") = Range("AG2")
    Else
    Range("AI2") = Range("X2") - Range("AB2")
    End If

Now were i have to check multiple country list
KSA
Qatar
Kuwait
Oman
UAE
Bahrain

I created table with above list of country and name the table "CountryList"

Macro has to check E2 = CountryList

Please suggest me how to modify above macro.

Thank you

 

[Chihiro]

Use Range("CountryList") to access named range. Then use either, Application.Match(value,range,0) or Evaluate method. If Application.Match() returns number, then it's match to one of the country name in the list.

 

[trprasad78]

Use Range("CountryList") to access named range. Then use either, Application.Match(value,range,0) or Evaluate method. If Application.Match() returns number, then it's match to one of the country name in the list.

If i give range("countrylist") it will check all list of country ?
i want store this countrylist in x and need to use where ever i want

 

[trprasad78]

I need to store countrylist in some x value and recall when ever i want to check.
because my macro will open different files and check , Once i open another file hope countrylist wont work on other file.
so i have to store countrylist in some x and call frequently.

How to store list of country in x and recall , it has to check each and every country if any one match it as to do given calculation.
if not need to do else.

 

[Chihiro]

Range("CountryList") will retain range. As it's referring to entire named range.

If you need to, store it as Range object or variant array. But there really is no need to.

 

[trprasad78]

Range("CountryList") will retain range. As it's referring to entire named range.

If you need to, store it as Range object or variant array. But there really is no need to.

thank you let me check and get back

 

[trprasad78]

If Range("E2") = ThisWorkbook.Worksheets.Range("CountryList") Then

table in Mainfile, Macro open some 10 files one by one and run this macro.
Please check above code not working

compiler Error "Method or data member not found"

Please suggust

 

[trprasad78]

even this is not working

If Range("E2") = "India" Or "South Korea" Or "Singapore" Or "KSA" Then

Range("K2") = Gtotal
Else

 

[Chihiro]

1. Like I said, you should use Application.Match() or Evaluate etc to compare value to list of values (i.e. range/list). You can't compare range object to value.

As well, you should explicitly refer to which workbook an object belongs to and what property (ex: Workbooks("SomeName").Sheets("SomeName").Range("E2").Value, or use with statement). Otherwise, it will be prone to error.

2. Syntax is off for your If statement. You need to repeat Range="string" for each OR.

I'd suggest uploading sample workbooks, that mirror your set up. If you require further help.

 

[trprasad78]

1. Like I said, you should use Application.Match() or Evaluate etc to compare value to list of values (i.e. range/list). You can't compare range object to value.

As well, you should explicitly refer to which workbook an object belongs to and what property (ex: Workbooks("SomeName").Sheets("SomeName").Range("E2").Value, or use with statement). Otherwise, it will be prone to error.

2. Syntax is off for your If statement. You need to repeat Range="string" for each OR.

I'd suggest uploading sample workbooks, that mirror your set up. If you require further help.

Sure will do soon thank you.

 

[trprasad78]

Hi I have uploaded sample file with vba code.

Please check and do the needful.