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