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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 05:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com