Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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: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 -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condition IF problem in Excel VBA
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: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 -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condition IF problem in Excel VBA
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with multiple condition in SUMIF | Excel Discussion (Misc queries) | |||
If Condition Problem | Excel Worksheet Functions | |||
Vlookup using if condition or any other way to slove problem | Excel Worksheet Functions | |||
countif condition problem | Excel Worksheet Functions | |||
Problem with IF condition or vector lookup? | Excel Worksheet Functions |