Condition IF problem in Excel VBA

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
("CHAMP_DÉBUT_BD").Offset(1, 0), Worksheets("UE").Range("A65536").End(xlUp))

' Set PI data range
Set rngPIData = Worksheets("PI").Range(Worksheets("PI").Range
("CHAMP_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:AD1") =
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

--
Message posted via http://www.officekb.com

Hi,
Try changing the following:

Dim IDUform as Long ' it is not a range ... or is text (cell format is
General)

IDUform = Worksheets("Formulaire").Range("AB2").Value
OR
IDUform = Worksheets("Formulaire").Cells(2, 28).Value

If Trim(UCase(rngPI)) = Trim(UCase(rngUE)) And _
Trim(UCase(rngPI)) = Trim(UCase(IDUform)) _
Then


Running with these changes populated the Formulaire sheet.

You code might be more efficient if you sorted both lists by IDU and used
FIND or Match to get corresponding values. Use IUDForm as the searchkey.

HTH


"Jean-Jérôme Doucet via OfficeKB.com" 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
> ("CHAMP_DÉBUT_BD").Offset(1, 0), Worksheets("UE").Range("A65536").End(xlUp))
>
> ' Set PI data range
> Set rngPIData = Worksheets("PI").Range(Worksheets("PI").Range
> ("CHAMP_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:AD1") =
> 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
>
> --
> Message posted via http://www.officekb.com
>

Thank you a lot! Your answer was helpful a lot!

Have a good day!

Jean-Jérôme Doucet

--
Message posted via http://www.officekb.com