Data validation

Question

Hi,
Within Excel, we can have data validation for the cells' values using a named list that is from the same workbook/sheets. How to programmably check if one cell value is within the named list, using VBA?

One thing more is, I remember that we can sort by several columns on a selected range (suppose that the range covers several columns) using Selection.Sort, in VBA. Am I right?

---

Many Thanks & Best Regards, HuaMin Chen

Answer 1

Hi

If you want to know if a data is in the list or not, the best easy way ,I think, is WorksheetFunction.CountIf.

WorksheetFunction.COUNTIF(range/list,criteria)

HTH

SJOO

---

the best time to plant a tree was twenty years ago. the second best time, is today (Chinese proverb) sjoo.kwak at gmail.com

Answer 2

Many thanks. I do have a problem with this line
        If WorksheetFunction.CountIf(Mainlist, Trim(Cells(row, 1).Value)) = 0 Then

where the range name 'Mainlist' is created within the sheets

---

Many Thanks & Best Regards, HuaMin Chen

Answer 3

Hi

I think you should write the code like this:

If WorksheetFunction.CountIf(Range("Mainlist"), Trim(Cells(row, 1).Value)) = 0 Then

HTH

SJOO

---

the best time to plant a tree was twenty years ago. the second best time, is today (Chinese proverb) sjoo.kwak at gmail.com