PDA

View Full Version : Process all values in search area to retrieve all matches



danlu
08-31-2009, 05:11 AM
Hi I have a Sub which goes through a range and for every cell in this range it calls the function "findfunction" in order to check if the same values exist in the area which the "findfunction" goes through.
If true then a cell range reference should be retrieved into Sheet1.

This works to retrieve the first match found. But if there is a match, then the "fundfunction" stops and the sub test() moves on to the next value to evaluate against the "findfunction".

The improvement needed is to make the the scan made by the "findfunction" to continue also after the first match, that is, to go on and find further potential matches for the value sent to the "findfunction" from the sub test() even after the first match. So all matches in the area scanned by the "fundfunction" are retrieved and entered into separate cells on the row which contains the value from sub test() which had a match in the "findfunction".



Sub test()
Dim a As Variant
Dim b As Variant
For Each a In Worksheets("Sheet1").Range("A1:A40").Cells
a.Offset(0, 4).Value = findfunction(a)
Next
End Sub

Function findfunction(ByVal a As Range)
Dim c As Variant
For Each c In Worksheets("Sheet2").Range("A1:A5").Cells
If c.Value = a.Value Then 'if true, evaluate 2nd if statement as well
If c.Offset(0, 1).Value = a.Offset(0, 1).Value Then findfunction = c.Address '2nd if condition
End If
Next
End Function

mdmackillop
08-31-2009, 05:43 AM
How many rows are you actually working with, and where do the additional results go?

danlu
08-31-2009, 06:38 AM
Hi in this example A1:A5 in the "fundfunction" is only an example. In the real world it will be a couple of hundred rows that the "fundfunction" should go through for every value which is passed to the "findfunction" from the Sub test()

If there is a match for ex for value in cell A2 (and therefore also in cell B2 since only when both criterias are met should a value be retrieved) then the retrieved value should be entered into cell a.Offset(0, 4). In case there is a second match then that retrieved value should be entered into a.Offset(0, 5). A third match should be entered into a.Offset(0, 6) and so on.

mdmackillop
08-31-2009, 06:44 AM
I would look at filtering the second sheet for each value, then looping through the visible cells to get the addresses.

danlu
08-31-2009, 06:51 AM
Do you mean some sort of automated filtering? Since there could be a lot of values also in the sub test() (on Sheet1) that should be passed one at a time to the "findfunction".