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:

Dim rngSource As Range
Dim rngFind As Range
Dim sArr() As String

'...your preceding code

    With Sheets("Summary")
        Set rngSource = .Range("E2", .Range("E2").End(xlDown))
        'Pass rng to array
        sArr = rngSource.Value
    End With
    
    For i = LBound(sArr) To UBound(sArr)
        With Sheets("Sheet1").Range("A:A")
            Set rngFind = .Find(What:=sArr(i), _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                '...do whatever you wanted to do once the match was found
                Else
                '...do something else
                End If
        End With
    Next i

[chouston]

How would I close the For loop in your example?

[AlvaroSiza]

It closes itself via

next i

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]

I'm getting a Type Mismatch for this line

        sArr = rngSource.Value

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

[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!

[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

            Set rngFind = .Find(What:=sArr(i), _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)