Werner,
Aready replied to this posting from Jean-Jerome.

"Werner" wrote:


Hi,

I have a code to compare two lists. It takes out the values on one of
the list that match the other table's list. I want to add a third
condition for the data to be retained and copied in my form.

It must also add the condition that each corresponding value of this
line of code must all meet the condition that it equals
worksheets("Formulaire").range("AB2).Value. I write in my form at "AB2"
the IDU and then while comparing the two IDU list from the "UE" and "PI"
lists, I also check if the matching rows also meet the the condition
that it equals the value in cell "AB2" of sheet "Formulaire". It would
look something like

If Trim(UCase(rngPI)) = Trim(UCase(rngUE)) =
worksheets("Formulaire").range("AB2).Value Then

Or something like

If Trim(UCase(rngPI)) = Trim(UCase(rngUE)) AND Trim(UCase(rngPI)) =
worksheets("Formulaire").range("AB2).Value Then

But it's weird. Written like that, I doesn't get any answers at all. So
there's must be an error. It's like the code doesn't catch the value of
AB2 at all and nothing matches.

Here's the complete code so far and after that, there's a link to a
sample of my Excel file with explanations :

Sub Transfer_PI_Data()

' Local Variables
Dim rngUEData As Range, rngUE As Range, rngPIData
As Range, rngPI As Range

' Set UE data range
Set rngUEData =
Worksheets("UE").Range(Worksheets("UE").Range("CHA MP_DÉBUT_BD").Offset(1,
0), Worksheets("UE").Range("A65536").End(xlUp))

' Set PI data range
Set rngPIData =
Worksheets("PI").Range(Worksheets("PI").Range("CHA MP_DÉBUT_BD").Offset(1,
0), Worksheets("PI").Range("A65536").End(xlUp))

Dim IDUform As Range

'*******The following line is pointin to the cell that contains the
third condition value. ******
IDUform = Worksheets("Formulaire").Range(28, 2).Value

' Transfer PI data for each UE data entry
Application.Calculation = xlCalculationManual
For Each rngUE In rngUEData
For Each rngPI In rngPIData

' ****It's here, in the following line, that I want rngPI and rngUE
matching data to be equal to the third specified value at AB2 in sheet
"formulaire" ****

If Trim(UCase(rngPI)) = Trim(UCase(rngUE)) Then
If Worksheets("Formulaire").Range("A12") = "" Then

Worksheets("Formulaire").Range("A12").Range("A1:AD 1") =
rngPI.Range("A1:AD1").Value
Else

Worksheets("Formulaire").Range("B65536").End(xlUp) .Offset(1,
-1).Range("A1:AD1") = rngPI.Range("A1:AD1").Value
End If
End If
Next rngPI
Next rngUE
Application.Calculation = xlCalculationAutomatic

End Sub

http://agora.ulaval.ca/~jjdou/Loclis04sample.zip

Bye!

Werner


--
Werner
------------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=379431