• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding matching values in Arrays:

Abhijeet R. Joshi

Abhijeet R. Joshi

Active Member
Hi all,

Just want to check whether there is an option to find specific values from arrays into already available arrays.
For instance, I want to define an array from a specific range in my worksheet (which is not constant, it might have 10 values or 100 or 145,etc..) and then provide the user with multiple select combobox (which is linked to some other data in some other sheet)to select desired values and then I want the macro to check the selected values from the combobox into the defined array and provide with a combined msgbox with the selected values and true/false in front of them..
Also it would be great if someone could let me know what is the limit for an array in MS Excel 2010.

Thanks in advance!!:):)
 
Thanks again Bobhc...
These specifications are much more then I expected....
This will surely help me out will learning the arrays..

Thanks a ton..
 
I still have the question open for capturing the values as arrays if they do not follow the conditions and then put them down on a msgbox for the end user..

Say I have A1:A3 as Yes Yes No and I run a code to reflect Yes and hence cell A3 does not follow the code and behaves like an exception hence I want the array to hold the value besides A3 which is B3 and provide it to me in an msgbox.

This time I would prefer syntax of the codes....;)
Reading requires an active mind however codes can be understood even when I am half asleep.;)
 
You mean like this:
Code: 
Public Sub GetList()
'will work for 70000 elements
MsgBox Join(Filter(Application.Transpose([IF(A1:A3<>"No",B1:B3,"~")]), "~", False), vbCrLf)
End Sub
 
I had joined array to build string for showing it in the message box. Try:
Code: 
Public Sub GetArray()
Dim a As Variant
Dim i As Long
'Join code is removed and now a is array
a = Filter(Application.Transpose([IF(A1:A3<>"No",B1:B3,"~")]), "~", False)
For i = LBound(a) To UBound(a)
    Debug.Print a(i)
Next i
End Sub
 
Thanks a lot Shri for this code..
Sorry for being slow on this I am quite new to vba and mostly to arrays...:)
Can you also help me avoid blanks from my range?
Say my range i.e. A1:A3 has a blank at A2 so I don't want it to consider the blank value while comparing and give me the array which has the values only.
 
Totally depends on the way you post requests ;)
Abhi said:
This time I would prefer syntax of the codes....;)
Reading requires an active mind however codes can be understood even when I am half asleep.;)
Click to expand...

Here's revised approach:
Code: 
Public Sub GetArray2()
Dim a As Variant
Dim i As Long
'Join code is removed and now a is array
a = Filter(Application.Transpose([IF(A1:A3<>"No",IF(A1:A3<>"",B1:B3,"~"),"~")]), "~", False)
For i = LBound(a) To UBound(a)
    Debug.Print a(i)
Next i
End Sub
 
You must log in or register to reply here.
Back
Top