Matching data macro in VBA Excel


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Matching data macro in VBA Excel

  1. #1
    Join Date
    Jun 2005
    Posts
    4

    Question Matching data macro in VBA Excel

    Hi,

    I am not advanced at programming macros manually. My Excel workbook act based on the simple principles of two tables in MS Access. The macro I need to create need to search into a database from two diffrent sheets of Excel, "UE" and "PI" and to copy the rows of "PI" that match the UE IDU (Identification). A UE IDU correspond to a building and PI rows corresponding to this IDU are housings inside the buiding. The number of housing depends of the buiding. One UE could have 3 or 4 or even 100 housings. So in details what is used to do the matching is the IDU colomn in colomn "A" of sheets "UE" and "PI". You could call that an index I think. Then, all the matching rows of "PI", from colomn "B" to colomn to "AD" of sheet "PI" must be written in a sheet named "Formulaire" (French name for form), starting from cell A12. So the matching rows of "PI", starting from A8 to a range AD8000, must be listed in sheet "formulaire" at A12 and folowwing rows without the A colomn IDU from "PI" (I need colomn B to AD from "PI" to be displayed in sheet "formulaire".). Oh, and for the IDU we want to throw in the search, he is located at cell AB2 of sheet "Formulaire".

    I know it's a looping for next with condition if and a break when there's no more data written (data that will increase in term of rows monthly) and then copying the matching rows into a selected area (A12 to AC8000 (without "PI"'s colomn "A" IDU)). It's just on the exact lines of code that I am stuck. I would be really glad if you could help me on that one guys.

    To help you, I have created a sample of my database. Here's the link :

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

    Thanks!

    Werner

  2. #2
    Join Date
    Jun 2005
    Posts
    4

    Condition IF problem in Excel VBA

    Since the last post, I know have a code that do mostly what I need. But there's still a problem.

    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

  3. #3
    Join Date
    Jun 2005
    Posts
    4

    Exclamation Matching data solution found

    Hi,

    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("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 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: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
    End If
    Next rngPI
    Next rngUE
    Application.Calculation = xlCalculationAutomatic


    End Sub

    Bye!

    Werner

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center