Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Range("C1") is in MyArray
Being very unfamiliar with arrays, but impressed with their worth, I am
trying to code with an array. I have defined MyArray, and now want to check to see if the cell value in "C1" is the same as any of my array values. How do I code that? Say: MyArray("one", "two", "three", "four", "five", "six", "seven", "eight") If Range("C1").value = [...is in MyArray] then (do something) End How do I test for C1's value being in MyArray? (Sorry if this is unclear; I am very trying...) TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Range("C1") is in MyArray
My usual approach is
If IsNumeric(Application.Match(Range("C1").Value, MyArray, 0)) Then 'Is in the array End If -- Regards, Juan Pablo González "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... Being very unfamiliar with arrays, but impressed with their worth, I am trying to code with an array. I have defined MyArray, and now want to check to see if the cell value in "C1" is the same as any of my array values. How do I code that? Say: MyArray("one", "two", "three", "four", "five", "six", "seven", "eight") If Range("C1").value = [...is in MyArray] then (do something) End How do I test for C1's value being in MyArray? (Sorry if this is unclear; I am very trying...) TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Range("C1") is in MyArray
Thanks, Juan, for your help.
I'm too ignorant to use your method, but will try it when I understand it. The following code seemed to "work" for me: For H = 1 To 12 If Range("F" & N) = MyArray(H) Then (do something) End If Next st. "Juan Pablo Gonzalez" wrote in message ... My usual approach is If IsNumeric(Application.Match(Range("C1").Value, MyArray, 0)) Then 'Is in the array End If -- Regards, Juan Pablo González "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... Being very unfamiliar with arrays, but impressed with their worth, I am trying to code with an array. I have defined MyArray, and now want to check to see if the cell value in "C1" is the same as any of my array values. How do I code that? Say: MyArray("one", "two", "three", "four", "five", "six", "seven", "eight") If Range("C1").value = [...is in MyArray] then (do something) End How do I test for C1's value being in MyArray? (Sorry if this is unclear; I am very trying...) TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Range("C1") is in MyArray
My method works by using the MATCH() worksheet function. Basically, in
Excel you would use it like =MATCH(C1, A1:A100, 0) to look for the value of C1 in the array A1:A100, using an exact match (0 as the third parameter) If the value is found, MATCH() returns a number (the position of C1 inside the array), else, it return #N/A. So, what the code does is check the result of MATCH(), if its a number, then your value is in the array, if not, then it will return an error, which means that it's not in it. -- Regards, Juan Pablo González "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... Thanks, Juan, for your help. I'm too ignorant to use your method, but will try it when I understand it. The following code seemed to "work" for me: For H = 1 To 12 If Range("F" & N) = MyArray(H) Then (do something) End If Next st. "Juan Pablo Gonzalez" wrote in message ... My usual approach is If IsNumeric(Application.Match(Range("C1").Value, MyArray, 0)) Then 'Is in the array End If -- Regards, Juan Pablo González "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... Being very unfamiliar with arrays, but impressed with their worth, I am trying to code with an array. I have defined MyArray, and now want to check to see if the cell value in "C1" is the same as any of my array values. How do I code that? Say: MyArray("one", "two", "three", "four", "five", "six", "seven", "eight") If Range("C1").value = [...is in MyArray] then (do something) End How do I test for C1's value being in MyArray? (Sorry if this is unclear; I am very trying...) TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |