Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing a value to a list
i need to check if the value of a cell is contained within a list
ex: cell.value is "apple" the list is stored as a range of cells, and is something like: "apple", "pear", "lemon" so the result would be TRUE (apple is in the list) however, if the cell value was "grapefruit', the result would be FALSE ------------------------------------ i would normally go thru the list using Find(), and FindNext(); if the value is found, =TRUE; if nothing =FALSE... the problem is that i'm already inside a FindNext() loop; so i get a conflict between the search string in the inner loop and the outer loop, which is being re-defined by the inner loop ------------------------------------- the list is not long, so i could do: if cell = range(a1)... or cell = range(a2)... or cell = range(a3)... or ... this seems to not be the most efficient way to do this ------------------------------------- i could also loop through each cell in the list comparing it's value to the searach string but, again, i think that there must be a more elegant solution (i think ?) mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
comparing a value to a list
mark kubicki wrote:
i need to check if the value of a cell is contained within a list ex: cell.value is "apple" the list is stored as a range of cells, and is something like: "apple", "pear", "lemon" so the result would be TRUE (apple is in the list) however, if the cell value was "grapefruit', the result would be FALSE ------------------------------------ i would normally go thru the list using Find(), and FindNext(); if the value is found, =TRUE; if nothing =FALSE... the problem is that i'm already inside a FindNext() loop; so i get a conflict between the search string in the inner loop and the outer loop, which is being re-defined by the inner loop ------------------------------------- the list is not long, so i could do: if cell = range(a1)... or cell = range(a2)... or cell = range(a3)... or ... this seems to not be the most efficient way to do this ------------------------------------- i could also loop through each cell in the list comparing it's value to the searach string but, again, i think that there must be a more elegant solution (i think ?) What about using VLOOKUP and check if the result is #N/A with ISNA? Regadrs, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
Comparing List A to List B and add what's missing from List B | Excel Discussion (Misc queries) | |||
comparing names in a list | Excel Discussion (Misc queries) | |||
Comparing 2 list | Excel Discussion (Misc queries) | |||
Comparing value to list | Excel Programming |