Finding Values Contained in an Array in a Sheet

[sperry2565]

Is this even possible? I have a user enter whatever they want to find in Column A of a random worksheet they are working in. I have written code to store this as an array

Please Login or Register  to view this content.

The add-in opens the file(s) that the user wants to pull data from. The ElmArr is what the code is to search for; once it's found it pull data from the same row but a different column and stores that in another array. How can this be done?

[patel45]

your question is too vague, attach a sample file with data and desired result.
You don't need array for finding a value in a column.

[sperry2565]

I'm trying to avoid posting my code due to proprietary reasons. I'm also trying to avoid using vlookup and match.

All I want it to do is take the array I've created, open the specified file, search for those numbers in the array, create a new array with the numbers associated with what it's found:

user created array to search file = G, J

file:

A |B |C
-----------
G |5 |2
G |5 |8
H |4 |3
H |4 |9
I |8 |4
I |8 |5
J |9 |1
J |9 |6


The code searches for the values specified, in this case G and J. There will be double of the entries because one will have a max and min value associated with it. I need to to find G and take the max value between the two G's in column C. Same thing with value J. So the answer I should get back is 8, 6. Once this is done and stored in another array I'll paste it to another sheet with it's corresponding user created array. Does this explanation clarify things for you?

[sperry2565]

Curious if anyone in the EF land can help me?
Thanks, Sam

[watersev]

hi Sam, sort of that within one workbook basing on your story above

[sperry2565]

Thank you! I kept hitting a wall by using find within a for loop and then offsetting the cell once it found it...it was going nowhere fast. It works beautifully...if the array is contained in A1. I tried to adapt the code to the column of A and it kept giving me a type mismatch.

[watersev]

sample, please check attachment

[sperry2565]

Considering how much I use xlDown you'd think I'd realize xlUp and rowcount would be a good idea. Brilliant, thank you!

[sperry2565]

I have a quick questions, I think. If I wanted to grab another cell with it. Say everything was pushed to the right 2 columns so what used to be "A" is not "C" and I want the max value as before but I also want what is contained in column "B", do I need to nest a for loop in there or is there an easier way?

edit: Basically if I had multiple columns of data and I just wanted to pull out 2 columns instead of just one based on the search array that aren't next to each other.

I've been looking in ReDim Preserve. Would there be a way to do that and then have it cycle through again and add that extra column? Or is that a less efficient way?

[sperry2565]

So I attempted to add an "And" after the then so pull more but I keep getting a Loop Without Do error....

But if I end just the if statement and add another if statement based off the answer from the first it works perfectly heh

Please Login or Register  to view this content.

[sperry2565]

So this didn't occur to me until I started really getting into the rest of my userform, but I realized that this just pulls the second entry for the same letter. So no matter which on of the G's or I's or J's etc... are bigger if just pulls the second entry for it. Is there a way to modify this so that it pulls the max value between the two entries?

[sperry2565]

Bump... anyone maybe?

[watersev]

Please Login or Register  to view this content.

[sperry2565]

I kept trying something similiar to that last night....I was just adding the .offset to the other side of value. Thank you!

[sperry2565]

I'm going to attempt to make this more complicated...what if it contains both positive and negatives values and you need the biggest number regardless of sign?