Hi,

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

It must also add the condition that each corresponding value of thi
line of code must all meet the condition that it equal
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 conditio
that it equals the value in cell "AB2" of sheet "Formulaire". It woul
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. S
there's must be an error. It's like the code doesn't catch the value o
AB2 at all and nothing matches.

Here's the complete code so far and after that, there's a link to
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 th
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 rngU
matching data to be equal to the third specified value at AB2 in shee
"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!

Werne

--
Werne
-----------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...fo&userid=2430
View this thread: http://www.excelforum.com/showthread.php?threadid=37943

Forget about it, I've found my error. Here's the working code for
matching two data lists and matching the matching results with another
specified code number to have only the results matching this third
condition :

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 Long

IDUform = Worksheets("Formulaire").Range("AB2").Value

' Transfer PI data for each UE data entry
Application.Calculation = xlCalculationManual
For Each rngUE In rngUEData
For Each rngPI In rngPIData
If Trim(UCase(rngPI)) = Trim(UCase(rngUE)) Then
If rngPI = IDUform 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
End If
Next rngPI
Next rngUE
Application.Calculation = xlCalculationAutomatic


End Sub

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

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