For each cell in one column search for match in entire differnet coloumn

[chouston]

I have a workbook with two sheets, I'm trying to figure out how to make it take use the value from each cell in column E of sheet 2 and search all of column A in sheet one, then if a matching value is found run a different function... I've tried a number of methods I have found and something goes really wrong every time I try and make them work.... Any suggestions?

[FDibbins]

You have this in the VBA forum, but I think you can probably do this with a simple vlookup or index/match?

If you upload a sample workbook, showing your data, I will take a look at it for you

[chouston]

I am trying to run it as part of a more complex VBA code, but I'm VERY new to this and have been stuck on this part for a while.... In the uploaded file the columns should be as described, sheet 2 is called Summary and Sheet1 is just that... the values I am trying to compare are invoice numbers. Thanks for any help!

[AlvaroSiza]

Maybe something like this:

Please Login or Register  to view this content.

[chouston]

How would I close the For loop in your example?

[AlvaroSiza]

It closes itself via

Please Login or Register  to view this content.

when (and where) i equals the upper bound (or count) of the array, which represents the data encompassing Range("E2:67") in your example.

[chouston]

Thanks, for some reason when I viewed it previously your code ended at this line:

'...do whatever you wanted to do once the match was found


Thank you, I'll let you know how it goes!

[chouston]

I'm getting a Type Mismatch for this line

Please Login or Register  to view this content.

Is this because sArr is a string and rngSource is a range?

[AlvaroSiza]

Possibly. I threw this together pretty quick.

If sArr = rngSource doesn't work (without the .Value), change sArr() as String to sArr() as Variant when dimensioning.

[chouston]

Hmmm, I tried both... removing the .Value yields the same error and the Variant yields an error 9 subscript out of range for this block

Please Login or Register  to view this content.